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;