Oracle Invalid Objects

Jan 20, 2016 / Updated on Jan 20, 2016

Get List of Invalid Objects

Following query can be used to get the list of Invalid objects.

SELECT object_type, object_name
FROM user_objects
WHERE STATUS != 'VALID'
ORDER BY object_type;

or

SELECT owner, object_type, object_name
FROM dba_objects
WHERE STATUS != 'VALID'
ORDER BY owner, object_type;

Compile Invalid Objects

Following SQL block can be used for compiling invalid objects

DECLARE
   sql_statement varchar2(500);
BEGIN
   dbms_output.put_line(chr(0));
   dbms_output.put_line('Re-compilation of Invalid Objects');
   dbms_output.put_line('---------------------------------');
   dbms_output.put_line(chr(0));

   FOR invalid in (SELECT object_type, object_name
                   FROM   user_objects o
                   WHERE  o.status       = 'INVALID'
                        AND  o.object_type IN ('PACKAGE', 'PACKAGE BODY', 'TRIGGER','VIEW','MATERIALIZED VIEW','PROCEDURE')
                   ORDER  BY o.object_type
                   )
   LOOP
      IF invalid.object_type = 'PACKAGE BODY' THEN
         sql_statement := 'alter package ' || invalid.object_name || ' compile body';
      ELSE
         sql_statement := 'alter ' || invalid.object_type || ' ' || invalid.object_name||' compile';
      END IF;

      BEGIN
           EXECUTE IMMEDIATE sql_statement;
           dbms_output.put_line(rpad(initcap(invalid.object_type) || ' ' || invalid.object_name, 32) || ' : compiled');
      EXCEPTION
      WHEN OTHERS THEN
           dbms_output.put_line(sqlerrm);
      END;
   END LOOP;
END;
/

or

SET heading off;
SET feedback off;
SET echo off;
SET lines 999;

SPOOL run_invalid.sql

SELECT 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
FROM dba_objects
WHERE STATUS = 'INVALID' AND object_type IN ('PACKAGE', 'FUNCTION', 'PROCEDURE');

SPOOL off;

SET heading on;
SET feedback on;
SET echo on;

@run_invalid.sql

or

EXEC UTL_RECOMP.recomp_serial('schema name');
comments powered by Disqus
Categories:
Tags: