Ciberterminal Wiki

User Tools

Site Tools

Trace:
dba:oracle:scripts:recompile_it_all

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
dba:oracle:scripts:recompile_it_all [2023/01/31 08:29] – removed - external edit (Unknown date) 127.0.0.1dba:oracle:scripts:recompile_it_all [2023/01/31 08:29] (current) – ↷ Page moved from dba:oracle:recompile_it_all to dba:oracle:scripts:recompile_it_all dodger
Line 1: Line 1:
 +====== [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>