項目中開發(fā)使用了VPD,數(shù)據(jù)庫用戶B的對象的創(chuàng)建依賴于數(shù)據(jù)用戶A,由于用戶A的對象進行DDL、遷移或dump等操作，造成了用戶B的對象INVALID.應用系統(tǒng)的數(shù)據(jù)源使用了用戶B,因此造成應用系統(tǒng)出錯。

此時可進行如下處理：

1,找到失效的對象

[sql]

select object_type,object_id,object_name

from user_objects

where status=’INVALID’ order by object_type

2,進行判斷后，可以重新編譯這些對象。

編譯的方法有多種：

1) DBMS_DDL

2.)DBMS_UTILITY

3.)UTL_RECOMP

4)UTLRP.SQL

5)Manually Recompile

最佳方案是手動編譯這些對象，可以參考下面的SQL腳本：

[sql]

Spool recompile.sql

Select ‘a(chǎn)lter ‘object_type’ ‘object_name’ compile;’

From user_objects

Where status <> ‘VALID’

And object_type IN (‘VIEW’,’SYNONYM’,

’PROCEDURE’,’FUNCTION’,

’PACKAGE’,’TRIGGER’);

Spool off

@recompile.sql

Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER

Spool pkg_body.sql

Select ‘a(chǎn)lter package ‘object_name’ compile body;’

From user_objects

where status <> ‘VALID’

And object_type = ‘PACKAGE BODY’;

Spool off

@pkg_body.sql

Spool undefined.sql

select ‘a(chǎn)lter materizlized view ‘object_name’ compile;’

From user_objects

where status <> ‘VALID’

And object_type =’UNDEFINED’;

Spool off

@undefined.sql

Spool javaclass.sql

Select ‘a(chǎn)lter java class ‘object_name’ resolve;’

from user_objects

where status <> ‘VALID’

And object_type =’JAVA CLASS’;

Spool off

@javaclass.sql

Spool typebody.sql

Select ‘a(chǎn)lter type ‘object_name’ compile body;’

From user_objects

where status <> ‘VALID’

And object_type =’TYPE BODY’;

Spool off

@typebody.sql

Spool public_synonym.sql

Select ‘a(chǎn)lter public synonym ‘object_name’ compile;’

From user_objects

Where status <> ‘VALID’

And owner = ‘PUBLIC’

And object_type = ‘SYNONYM’;

Spool off

@public_synonym.sql

總結(jié)

以上是生活随笔為你收集整理的Oracle数据库用户失效对象,Oracle数据库对象失效解决的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。