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.
SQL> ALTER FUNCTION TEST_FUNC COMPILE
Following query will give all the invalid objects from the schema.
SELECT ‘ALTER ‘ || object_type || ‘ ‘ || object_name || ‘ COMPILE;’
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.
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)
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.