Systems Engineering and RDBMS


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:

select object_name,object_Type,status
from user_objects
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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: