Tuesday, December 25, 2012

Generate script to compile invalid objects

Sometimes some database object(s) stays invalid (there is a lot of reason: modification object, compiling new procedure, function, package (depending exists) etc).

Easy method is compile @$ORACLE_HOME/rdbms/admin/utlrp.sql or detect invalid objects and recompile it. But it is dangerous every time to use utlrp, it is recommended to use utlrp when upgrade db version ex : 9 > 10 > 11 (also release upgrade). Because while upgrade process DB is not available to use for end user.

But if invalid object count is not big and not a few you can use below single SQL and compile result of it.

SQL> select 'ALTER TRIGGER '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='TRIGGER'
union
select 'ALTER FUNCTION '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='FUNCTION'
union
select 'ALTER PACKAGE '||OWNER||'.'||OBJECT_NAME||' COMPILE BODY;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='PACKAGE BODY'
union
select 'ALTER PROCEDURE '||OWNER||'.'||OBJECT_NAME||' COMPILE;'
from dba_objects where status='INVALID' AND OBJECT_TYPE='PROCEDURE'

EX:

ALTER FUNCTION ULFET.TEST_FUNC COMPILE;
ALTER PACKAGE ULFET.LOG_TOOL COMPILE BODY;
ALTER PROCEDURE ULFET.UPDATE_EMP COMPILE;
ALTER PROCEDURE ULFET.ADD_EMP COMPILE;
ALTER TRIGGER ULFET.TAKE_OLD_VALUE COMPILE;

Also you can use DBMS_DDL and DBMS_UTILITY packages:

EXEC DBMS_DDL.ALTER_COMPILE('PACKAGE', 'ULFET', 'LOG_TOOL');
or
EXEC DBMS_UTILITY.COMPILE_SCHEMA('ULFET');

Also you can create PLSQL anonymous block and compile it.

SQL> set serverouput on
SQL> DECLARE
   comp_pack        VARCHAR2 (100);
   comp_pack_body   VARCHAR2 (200);
   comp_view        VARCHAR2 (200);
   comp_proc        VARCHAR2 (200);
   comp_trig        VARCHAR2 (200);
   comp_func        VARCHAR2 (200);
BEGIN
   FOR c IN (  SELECT * FROM dba_objects WHERE status = 'INVALID' ORDER BY object_type)
   LOOP
      BEGIN
         --generate compile statement
         comp_pack :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_pack_body :='alter package '|| c.owner|| '.'|| c.object_name|| ' compile body;';
         comp_view :='alter view ' || c.owner || '.' || c.object_name || ' compile;';
         comp_proc :='alter procedure '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_func :='alter function '|| c.owner|| '.'|| c.object_name|| ' compile;';
         comp_trig :='alter trigger '|| c.owner|| '.'|| c.object_name|| ' compile;';
         DBMS_OUTPUT.put_line ('Compile -> ' || c.object_name || ' type : ' || c.object_type);
         --compile
         IF c.object_type = 'PACKAGE' THEN
            EXECUTE IMMEDIATE comp_pack;
         ELSIF c.object_type = 'PACKAGE BODY' THEN
            EXECUTE IMMEDIATE comp_pack_body;
         ELSIF c.object_type = 'VIEW' THEN
            EXECUTE IMMEDIATE comp_view;
         ELSIF c.object_type = 'PROCEDURE' THEN
            EXECUTE IMMEDIATE comp_proc;
         ELSIF c.object_type = 'FUNCTION' THEN
            EXECUTE IMMEDIATE comp_func;
         ELSIF c.object_type = 'TRIGGER' THEN
            EXECUTE IMMEDIATE comp_trig;
         END IF;
         --catch exception and show
      EXCEPTION
         WHEN OTHERS THEN
            DBMS_OUTPUT.put_line ('Compile ERROR : '|| c.owner|| '.'|| c.object_name|| ' type => '|| c.object_type);
      END;
   END LOOP;
END;


Read it in azerbaijani : http://www.slideshare.net/ulfettanriverdiyev/invalid-obyektlerin-kampilasiyasi

1 comment:

  1. SVR Technologies is the best online training institute which is offering all software IT courses at the lowest cost. SVR Technologies Offering the Shell Scripting for the best possible price with the quality assured trainers who are having a real time experience of more than 9 years. We are offering our services from past 10 years,
    If you want to know more about Shell Scripting, visit
    http://www.svrtechnologies.com/shellscript_training.html

    ReplyDelete