Oracle失效对象处理

jiawang

查看失效对象：


SELECT owner, object_name, object_type,status
FROM dba_objects
WHERE status = 'INVALID';
–编译无效对象脚本utlrp.sql
$ sqlplus ‘/as sysdba’ @?/rdbms/admin/utlrp.sql
如：


Sqlplus / as sysdba
SQL>alter session set container=hirain_test02;
SQL>@?/rdbms/admin/utlrp.sql
编译特定对象拼接语句


--自动生成视图重新编译语句
select owner, object_name, object_type, status  ,'alter view ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='VIEW'  order by t.owner,t.object_type;
--自动生成函数重新编译语句
select owner, object_name, object_type, status  ,'alter FUNCTION ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='FUNCTION'  order by t.owner,t.object_type;
--自动生成视物化图重新编译语句
select owner, object_name, object_type, status  ,'alter MATERIALIZED VIEW ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='MATERIALIZED VIEW'  order by t.owner,t.object_type;
--自动生成包重新编译语句
select owner, object_name, object_type, status  ,'alter PACKAGE ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='PACKAGE BODY'  order by t.owner,t.object_type;
--自动生成触发器重新编译语句
select owner, object_name, object_type, status  ,'alter TRIGGER ' || t.owner||'.' || object_name || ' compile'||';'
from dba_objects t  
where status='INVALID' and t.object_type='TRIGGER'  order by t.owner,t.object_type;