Oracle数据库用户失效对象,Oracle数据库对象失效解决

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

项目中开发使用了VPD,数据库用户B的对象的创建依赖于数据用户A,由于用户A的对象进行DDL、迁移或dump等操作，造成了用户B的对象INVALID.应用系统的数据源使用了用户B,因此造成应用系统出错。

此时可进行如下处理：

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 'alter '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 'alter 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 'alter materizlized view 'object_name' compile;'

From user_objects

where status <> 'VALID'

And object_type ='UNDEFINED';

Spool off

@undefined.sql

Spool javaclass.sql

Select 'alter java class 'object_name' resolve;'

from user_objects

where status <> 'VALID'

And object_type ='JAVA CLASS';

Spool off

@javaclass.sql

Spool typebody.sql

Select 'alter 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 'alter public synonym 'object_name' compile;'

From user_objects

Where status <> 'VALID'

And owner = 'PUBLIC'

And object_type = 'SYNONYM';

Spool off

@public_synonym.sql