Monday, September 5, 2011

How to compile Invalid Object?

Operations such as upgrades, patches and DDL changes can invalidate schema objects.Here are  some  methods  to compile   objects .But I prefer  UTL_RECOMP (from sql  or PL/SQL ) or  $ORACLE_HOME/rdbms/admin/utlrp.sql (From Operting system)
  1.  we  can  compile it manually like 
     alter  package serdar.xxx compile  body  ;
  2. Manuel by the help  of a  script:we can  spool  the output  and  run it.Note  that  this script is not written for all types  like java classes

    Set heading off;
    set feedback off;
    set echo off;
    Set lines 999;
    spool compile.sql

    select  'alter '||
    decode(object_type,'SYNONYM',decode(owner,'PUBLIC','PUBLIC SYNONYM '||object_name,
    'SYNONYM '||OWNER||'.'||OBJECT_NAME)||' compile;',
    decode(OBJECT_TYPE ,'PACKAGE BODY','PACKAGE',OBJECT_TYPE)||
    ' '||owner||'.'||object_name||' compile '||
    decode(OBJECT_TYPE ,'PACKAGE BODY','BODY;',' ;'))
    from dba_objects where status<>'VALID'
    order by owner,OBJECT_NAME;
    spool off
    @compile.sql


  3. DBMS_DDL.ALTER_COMPILE: The same with "alter   procudere serdar.test compile"

    exec dbms_ddl.alter_compile ('PROCEDURE','SERDAR','TEST');
  4. DBMS_UTILITY.compile_schema:The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema

    EXEC DBMS_UTILITY.compile_schema(schema => 'SERDAR');
  5. UTL_RECOMP :it will  recompile all invalids.

    --Compile  all invalids  in the database
    exec  SYS.UTL_RECOMP.RECOMP_SERIAL ();
    --Compile schema  SERDAR
    exec SYS.UTL_RECOMP.RECOMP_SERIAL ('SERDAR');   

    --Compile all invalids in the database parallel (If we  have eneogh  CPU )

    exec  SYS.UTL_RECOMP.recomp_parallel(4);
  6. UTLRP.SQL  : From  the  operating  system as sysdba.Note that this is an example  for UNIX systems

    SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql