User Tools

Site Tools


dba:oracle:scripts:recompile_it_all

[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