dba:oracle:scripts:recompile_it_all
Table of Contents
[SCRIPT] Recompile all database objects
Description
How to compile the invalid objects
Instructions
Execute the following block code as sys:
SET echo off SET feed off SET linesize 200 col owner format a35 col Object_type format a35 col object_name format a35 prompt -- ################################# INVALID_OBJECTS SELECT owner, Object_type, object_name FROM dba_objects WHERE OWNER NOT IN ( 'SYSMAN', 'CTXSYS', 'MGMT_VIEW', 'TOOLS', 'SQLTXPLAIN', 'SYS', 'SYSTEM', 'DBSNMP' ) AND STATUS='INVALID' ; prompt -- ################################# COMPILING BEGIN FOR oname IN ( SELECT object_name, owner, CASE WHEN Object_type LIKE '% BODY' THEN REPLACE(OBJECT_TYPE, ' BODY', '') ELSE Object_type END AS Object_type FROM SYS.ALL_OBJECTS WHERE OWNER NOT IN ( 'SYSMAN', 'CTXSYS', 'MGMT_VIEW', 'TOOLS', 'SQLTXPLAIN', 'SYS', 'SYSTEM', 'DBSNMP' ) AND STATUS='INVALID' AND OBJECT_TYPE LIKE '% BODY' ) LOOP BEGIN EXECUTE IMMEDIATE 'ALTER ' || oname.object_type || ' ' || oname.owner || '.' || oname.object_name || ' COMPILE BODY'; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error compiling ' || oname.object_type || ' BODY ' || oname.owner || '.' || oname.object_name); END; END LOOP; FOR oname IN ( SELECT object_name, owner, CASE WHEN Object_type LIKE '% BODY' THEN REPLACE(OBJECT_TYPE, ' BODY', '') ELSE Object_type END AS Object_type FROM SYS.ALL_OBJECTS WHERE OWNER NOT IN ( 'SYSMAN', 'CTXSYS', 'MGMT_VIEW', 'TOOLS', 'SQLTXPLAIN', 'SYS', 'SYSTEM', 'DBSNMP' ) AND STATUS='INVALID' ) LOOP BEGIN EXECUTE IMMEDIATE 'ALTER ' || oname.object_type || ' ' || oname.owner || '.' || oname.object_name || ' COMPILE '; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Error compiling ' || oname.object_type || ' ' || oname.owner || '.' || oname.object_name); END; END LOOP; END; / prompt -- ################################# INVALID_OBJECTS SELECT COUNT(*) INVALID_OBJECTS FROM dba_objects WHERE owner='VOXEL' AND STATUS='INVALID' ;
dba/oracle/scripts/recompile_it_all.txt · Last modified: 2023/01/31 08:29 by dodger