Posted by decipherinfosys on March 21, 2009
In a previous blog post we had discussed how to compile invalid schema objects using the dbms_utility package. Oracle provides yet another way to re-compile invalid views, stored procedures, packages, java programs using the UTL_RECOMP pre-defined package. Using this package we can recompile invalid objects either sequentially or in parallel. First, run the query below to find out invalid objects in the schema. Connect to schema using SQL*Plus and run following sql:
where status = ‘INVALID’;
OBJECT_NAME OBJECT_TYPE STATUS ------------------------------ -------------------- ------- TEST_VIEW VIEW INVALID GET_TYPE FUNCTION INVALID TEST_FUNC FUNCTION INVALID TEST_PROC PROCEDURE INVALID TEST_PKG PACKAGE BODY INVALID
UTL_RECOMP has two procedures as its subprograms. RECOMP_SERIAL (to recompile all the objects serially) and RECOMP_PARALLEL (to recompile schema objects in parallel to leverage the existence of multi cpu environment.) Let us compile the invalid objects from schema ‘decipher’.
SQL> connect /as sysdba;
SQL> exec utl_Recomp.recomp_serial(‘DECIPHER’);
PL/SQL procedure successfully completed.
Run the query shown above to find invalid objects again. You should not see any invalid objects in the schema unless they are genuinely invalid. Similarly we can also execute recomp_parallel procedure as shown below. IT takes two parameters, schema name and number of threads. Number of threads normally equates to number of cpu on the server. Following is an example.
SQL> connect /as sysdba;
SQL> exec utl_Recomp.recomp_parallel(2,’DECIPHER’);
In both these procedures, if schema name is omitted, all the invalid objects of the database will be recompiled. Also both of these procedures have extra input parameter ‘flag’, which defaults to 0 and normally used for internal purpose only so we don’t have to worry about that. Notice that, we are connecting to SQL*Plus session with sysdba privilege. It has certain limitations.
• No other DDL should be running in database while running this package.
• User should be connected as sysdba.
• Package should always be executed from SQL*Plus.
• It also assumes that, dbms_job, dbms_standard and dbms_random package already exists and are in a valid state.
This package is more useful after migration or upgrade of the version which invalidates large number of objects.
- Oracle 11g PL/SQL Packages and Types Reference: – here.