Script to compile invalid objects in DB

最新推荐文章于 2021-04-15 14:58:11 发布

最新推荐文章于 2021-04-15 14:58:11 发布 · 83 阅读

文章标签：

这是一个用于Oracle数据库的PL/SQL脚本，旨在自动编译刷新后数据库中所有无效的对象，如过程、函数等。作者建议运行此脚本前进行测试，并由sysdba身份执行以避免权限问题。首次运行可能会较慢。

REM: Script to compile invalid objects in DB after refreshing
REM:
REM:*****************************************
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************

set pagesize 0
set feedback off
set trimspool on
prompt
prompt Run the script as sysdba otherwise invalid objects will remain (ORA-01031 error)
prompt
prompt Run the script several times. It only takes long the first time
prompt
prompt check the progress of compilation by issueing from another session
prompt select count(*) "invalid" from dba_objects where status <> 'VALID';
prompt
prompt hit <enter> to continue
pause
spool compile.lis
select 'alter '||object_type||' '||owner||'.\"'||object_name||'\" compile;'
from dba_objects
where status<>'VALID'
and object_type not in ('PACKAGE BODY','TYPE BODY','UNDEFINED','JAVA CLASS','SYNONYM')
union
select 'alter package '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='PACKAGE BODY'
union
select 'alter type '||owner||'.'||object_name||' compile body;'
from dba_objects
where status<>'VALID'
and object_type='TYPE BODY'
union
select 'alter materialized view '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='UNDEFINED'
union
select 'alter java class '||owner||'.\"'||object_name||'\" resolve;'
from dba_objects
where status<>'VALID'
and object_type='JAVA CLASS'
union
select 'alter synonym '||owner||'.'||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner<>'PUBLIC'
union
select 'alter public synonym '||object_name||' compile;'
from dba_objects
where status<>'VALID'
and object_type='SYNONYM'
and owner='PUBLIC';
spool off
set feedback on