Compile invalid objects per user connected
                                                 Last update (2008-03-27 14:36:51)
                                                                                                                   Date added (2008-02-29 09:51:20)

Summary
Operations such as upgrades, patches and DDL changes can invalidate schema objects. First action is to identify the invalid objects
SELECT owner, object_type, object_name, status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;
The simplest approach is the manual compiling of object, for example
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Or an alternative approach is to use the DBMS_DDL package to perform the recompilations:
EXEC DBMS_DDL('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');
EXEC DBMS_DDL('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');
EXEC DBMS_DDL('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');
EXEC DBMS_DDL('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
Tip: his method is limited to PL/SQL objects, so it is not applicable for views.

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
This script compiles all invalid objects of the user currently connected. If for example connect as user USER01, and run the script from sqlplus, then it will compile all the invalid objects in USER01 schema.
DECLARE
  object_type VARCHAR2(20);
  comp_param  VARCHAR2(20);
  cid PLS_INTEGER;
  CURSOR read_objects
  IS
    SELECT OBJECT_TYPE,OBJECT_NAME
    FROM USER_OBJECTS
    WHERE STATUS='INVALID';
BEGIN
  FOR ro IN read_objects LOOP
      IF ro.OBJECT_TYPE='PACKAGE BODY' THEN
        object_type := 'PACKAGE';
        comp_param := 'COMPILE BODY';
      ELSE
        object_type := ro.OBJECT_TYPE;
        comp_param := 'COMPILE';
      END IF;
      cid := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(cid,'ALTER '||object_type||' '||ro.OBJECT_NAME||' '||comp_param, DBMS_SQL.NATIVE);
      DBMS_SQL.CLOSE_CURSOR(cid);
  END LOOP;
END;
/
Other links
Compile invalid objects (UTL_RECOMP)
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search