Recompiling Invalid/All schema objects in the database

Identifying Invalid Objects: Use all_objects table to identity invalid schema objects. Oracle has given the default pkg to recompiling invalid/all schema objects in the database.

SELECT owner,
       object_type,
       object_name,
       status
  FROM all_objects
 WHERE status = 'INVALID'
 ORDER BY owner,
          object_type,
          object_name;

Custom Script: When you have to compile "N" no of invalid objects in one go, the best approach is to write custom script as below,

BEGIN
  FOR cur IN(SELECT object_name,
                    object_type,
                    owner
               FROM sys.all_objects
              WHERE object_type   = 'MY_OBJECTS'
                AND owner         = 'MY_SCHEMA'
                AND status        = 'INVALID' -- For all objects, it should be excluded
             )
  LOOP
    BEGIN
      IF cur.object_type = 'PACKAGE BODY' THEN
        EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner || '"."' || cur.object_name || '" compile body';
      ELSE
        EXECUTE IMMEDIATE 'alter ' || cur.object_type || ' "' || cur.owner || '"."' || cur.object_name || '" compile';
      END IF;
    EXCEPTION
    WHEN OTHERS THEN
      NULL;
    END;
  END LOOP;

END;

Manual Execution: Manual execution is sufficient, when you have to compile small no of objects.
  • ALTER PROCEDURE <<my_procedure>> COMPILE;
  • ALTER FUNCTION <<my_function>> COMPILE;
  • ALTER PACKAGE <<my_package>> COMPILE;
  • ALTER PACKAGE <<my_package>> COMPILE BODY;
  • ALTER VIEW <<my_view>> COMPILE;
  • ALTER TRIGGER <<my_trigger>> COMPILE;
DBMS_DDL.alter_compile: An alternative approach is to use the DBMS_DDL pkg,
  • exec dbms_ddl.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
  • exec dbms_ddl.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
  • exec dbms_ddl.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
  • exec dbms_ddl.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
  • exec dbms_ddl.alter_compile('VIEW', 'MY_SCHEMA', 'MY_VIEW');
  • exec dbms_ddl.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
DBMS_UTILITY.compile_schema: The DBMS_UTILITY.compile_schema procedure used to compiles all procedures, functions, packages, views, and triggers in the specified schema.

exec dbms_utility.compile_schema(SCHEMA => 'FXO', compile_all => FALSE);


Comments

Popular posts from this blog

Highlight the cell of Interactive Report based on Search Criteria in Oracle APEX

Querying Data From Flat Files in Oracle

Number of Tables by The Number of Rows in Oracle Database

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Highlight Data That Meet Certain Criteria in Oracle APEX Report

Return a Validation Message using JavaScript if the Oracle Application Express Item is Empty

Remove X (Close Mark) from Oracle APEX Modal Dialog

Display User Rating Icons Dynamically in Oracle APEX Report

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Find Where Specific Table or View is Used in Oracle Database