To compile an entire schema in Oracle, there are two utilities provided by Oracle.
DBMS_UTILITY.COMPILE_SCHEMA
This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges).
I am using Oracle 10g (10.2.0.3.0) for the following examples.
Syntax:
DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN);
Example (from SQL *Plus):
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT', compile_all => FALSE);
For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter):
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT');
UTL_RECOMP Package
This package has been introduced from Oracle 10g Release 2, which is specially designed for recompiling invalid objects. It recompiles PLSQL modules, Java procedures etc. Only a SYSDBA account can run the subprograms in this package. But always a DBA can grant EXECUTE privilege to your schema (or PUBLIC).
There are two ways in which invalid objects are recompiled if you choose to use this package.
By using the subprogram RECOMP_SERIAL for compiling invalid objects sequentially one by one. The syntax of the subprogram is as follows:
UTL_RECOMP.RECOMP_SERIAL( schema VARCHAR2, flags BINARY_INTEGER);
Simply call the program as follows:
EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');
The subprogram RECOMP_PARALLEL is available within this package UTL_RECOMP to enable multiple threads for recompiling invalid objects. The syntax of the procedure is as follows:
UTL_RECOMP.RECOMP_PARALLEL (threads BINARY_INTEGER, schema VARCHAR2, flags BINARY_INTEGER);
Note: By using more number of threads the recompiling could be slow taken to consideration the I/O intensive nature of recompiling invalid objects. There is no optimized number of threads to be used while recompiling, but as a rule of thumb use minimum number of threads as possible.
Example of usage of RECOMP_PARALLEL procedure:
EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');
Coming soon: Article on comparison of these procedures.
- DBMS_UTILITY.COMPILE_SCHEMA
- Two procedures in UTL_RECOMP
DBMS_UTILITY.COMPILE_SCHEMA
This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges).
I am using Oracle 10g (10.2.0.3.0) for the following examples.
Syntax:
DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN);
Example (from SQL *Plus):
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT', compile_all => FALSE);
For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter):
EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT');
UTL_RECOMP Package
This package has been introduced from Oracle 10g Release 2, which is specially designed for recompiling invalid objects. It recompiles PLSQL modules, Java procedures etc. Only a SYSDBA account can run the subprograms in this package. But always a DBA can grant EXECUTE privilege to your schema (or PUBLIC).
There are two ways in which invalid objects are recompiled if you choose to use this package.
- Sequentially
- Parallelly
By using the subprogram RECOMP_SERIAL for compiling invalid objects sequentially one by one. The syntax of the subprogram is as follows:
UTL_RECOMP.RECOMP_SERIAL( schema VARCHAR2, flags BINARY_INTEGER);
Simply call the program as follows:
EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');
The subprogram RECOMP_PARALLEL is available within this package UTL_RECOMP to enable multiple threads for recompiling invalid objects. The syntax of the procedure is as follows:
UTL_RECOMP.RECOMP_PARALLEL (threads BINARY_INTEGER, schema VARCHAR2, flags BINARY_INTEGER);
Note: By using more number of threads the recompiling could be slow taken to consideration the I/O intensive nature of recompiling invalid objects. There is no optimized number of threads to be used while recompiling, but as a rule of thumb use minimum number of threads as possible.
Example of usage of RECOMP_PARALLEL procedure:
EXEC UTL_RECOMP.RECOMP_SERIAL (schema => 'SCOTT');
Really?
ReplyDeleteYou really use DBMS_UTILITY.COMPILE_SCHEMA?
What about bogus time to recompilation complete schema?
So, utlrp.sql not invent yet?
Hi
ReplyDeletethanks for this. One question re the syntax of dbms_utility.compile_schema
Is there an option to call it for the current schema? i.e. dbms_utility.compile_schema(schema=> USER). It's not documented but I suspect it works. I'd love some confirmation on this.
thanks
Brian
Hi Brian, It works as you suspect. I dont see any reason if in future ORACLE will discontinue it.
ReplyDeleteI just did this experiment for you:
create table u as select user u from dual;
Table created
desc u
------
Name Type
---- ------------
U VARCHAR2(30)
desc dbms_utility.compile_schema
--------------------------------
Parameter Type
SCHEMA VARCHAR2
So it has no problem at all.
Also I tried this:
exec dbms_utility.compile_schema(USER);
PL/SQL procedure successfully completed
(No errors).
UTL_RECOMP.recomp_serial will not validate trigger.......??
ReplyDelete"Coming soon: Article on comparison of these procedures. "
ReplyDeleteI'd like to see this when it's published. . . .