Script to compile invalid objects in DB

最新推荐文章于 2025-11-15 15:24:55 发布

最新推荐文章于 2025-11-15 15:24:55 发布 · 157 阅读

文章标签：

本文提供了一段脚本用于在数据库刷新后清除无效对象，包括检查并清理各种类型的无效对象，如表、视图、存储过程等，并通过提示用户进行操作确保数据库的正常运行。

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