Differences

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

--- oracle:recompile_it_all [2018/04/27 08:57] – dodger
+++ dba:oracle:scripts:recompile_it_all [2023/01/31 08:29] – removed - external edit (Unknown date) 127.0.0.1
@@ 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>