Oracle数据库对象失效
Oracle数据库对象失效
项目中开发使用了VPD,数据库用户B的对象的创建依赖于数据用户A,由于用户A的对象进行DDL、迁移或dump等操作,造成了用户B的对象INVALID。应用系统的数据源使用了用户B,因此造成应用系统出错。
此时可进行如下处理:
1,找到失效的对象
- select object_type,object_id,object_name
- from user_objects
- where status='INVALID' order by object_type
编译的方法有多种:
1) DBMS_DDL
2.)DBMS_UTILITY
3.)UTL_RECOMP
4)UTLRP.SQL
5)Manually Recompile
最佳方案是手动编译这些对象,可以参考下面的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
评论暂时关闭