Systems Engineering and RDBMS

Back to the Basics: Compiling schema objects using dbms_utility package

Posted by decipherinfosys on February 6, 2009

There are various ways by which one can recompile any invalid objects in the schema. We can issue ALTER statements for each individual object or we can also write a  small SQL query, which will give us a list of all the invalid objects. We can save it as a  sql file and then run that sql file to compile all invalid objects. Following sqls show the above two approaches.

Compiling individually:

SQL> ALTER FUNCTION TEST_FUNC COMPILE
SQL> /

Function altered.

Following query will give all the invalid objects from the schema.

SELECT ‘ALTER ‘ || object_type || ‘ ‘ || object_name || ‘ COMPILE;’
FROM USER_OBJECTS
WHERE STATUS = ‘INVALID’;

Yet another approach to compile all the objects in schema is by using DBMS_UTILTY package. It has COMPILE_SCHEMA procedure. Following is the signature of the procedure.

DBMS_UTILITY.COMPILE_SCHEMA
(
SCHEMA VARCHAR2,
compile_all BOOLEAN DEFAULT TRUE,
reuse_settings BOOLEAN DEFAULT FALSE
);

If compile_all parameter is set to TRUE, it will re-compile all the objects of the schema irrespective of its status whether it is valid or invalid. If it is set to FALSE, it will re-compile only invalid objects.

IF reuse_setting parameter is set to TRUE, it will use current session settings (like optimization level, compile time warning etc.) for a specific object. If it is set to false, it will use object’s current setting. For more details on this, refer to Oracle documentation.

Here is the call to compile all the objects in schema keeping rest of the two parameters with default value.

SQL> call dbms_utility.compile_schema(USER)
2  /

Call completed.

When executed, it may give one of the following exceptions based on the error encountered.

ORA-20000 Insufficient privileges for some object in this schema
ORA-20001 Cannot recompile SYS objects
ORA-20002 Maximum iterations exceeded. Some objects may not have been recompiled.

Also, starting with 10g, Oracle introduced new built-in package UTL_RECOMP. This package has two procedures to recomp_serial and recomp_parallel to re-compile all invalid objects either serially or in parallel. Only caveat is one has to connect as SYSDBA in order to execute these procedures.

It is always to good practice to verify that all the objects are compiled after the procedure execution.

One Response to “Back to the Basics: Compiling schema objects using dbms_utility package”

  1. […] 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 […]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: