====== [SCRIPT] Recompile all database objects ======

====== Description ======


How to compile the invalid objects


====== Instructions ======


Execute the following block code as sys:


<code sql>
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' ;
</code>