Tag Archives: invalid

hp-ux, itanium, Oracle, Oracle 10g, oracle 9i, unix

Oracle UTL_RECOMP package and the RECOMP_SERIAL subprogram

Leave a comment

During the normal administration and operation of an Oracle database system, programming units within the database can become invalid for various reasons. To resolve this issue, the Oracle RDBMS provides the package UTL_RECOMP to recompile objects which are currently in an invalid status. The UTL_RECOMP package provides the administrator with 3 subprogram units: PARALLEL_SLAVE, RECOMP_PARALLEL, and RECOMP_SERIAL. This article covers the user of package and subprogram unit UTL_RECOMP.RECOMP_SERIAL and was written against an Oracle 10.2.0.4 RDBMS on RHEL.

1. Logon to you Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS as the sysdba user.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jul 3 10:24:46 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. The RECOMP_SERIAL subprogram unit recompiles all objects or objects within a schema in an ordered fashion. This means that any objects which are depended on by other objects are compiled first.

4. To compile all objects within the current database execute the command: execute utl_recomp.recomp_serial();

SQL> execute utl_recomp.recomp_serial();

PL/SQL procedure successfully completed.

SQL>

5. To compile only the objects in a single schema, specify the schema name: execute utl_recomp.recomp_serial(‘SCOTT’);

SQL> execute utl_recomp.recomp_serial('SCOTT');

PL/SQL procedure successfully completed.

SQL>

This completes the recompilation of objects using the package UTL_RECOMP with subprogram unit RECOMP_SERIAL.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com

11g, Oracle, Oracle 10g, oracle 9i

Oracle UTL_RECOMP package and the RECOMP_PARALLEL subprogram

Leave a comment

During the normal administration and operation of an Oracle database system, programming units within the database can become invalid for various reasons. To resolve this issue, the Oracle RDBMS provides the package UTL_RECOMP to recompile objects which are currently in an invalid status. The UTL_RECOMP package provides the administrator with 3 subprogram units: PARALLEL_SLAVE, RECOMP_PARALLEL, and RECOMP_SERIAL. This article covers the user of package and subprogram unit UTL_RECOMP.RECOMP_PARALLEL and was written against an Oracle 10.2.0.4 RDBMS on RHEL.

1. Logon to you Oracle database server as the Oracle software owner.

2. Connect to SQLPLUS as the sysdba user.

mylinux:> sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 30 19:58:23 2010

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3. The RECOMP_PARALLEL subprogram unit recompiles all objects or objects within a schema using a degree of parallelism specified in the command or by the init parameter JOB_QUEUE_PROCESSES. This means that if you specified a degree of parallelism of 4, than Oracle would attempt to recompile 4 objects at a single time.

4. To compile all objects in the database with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2);

SQL> execute utl_recomp.recomp_parallel(2);

PL/SQL procedure successfully completed.

SQL>

5. To compile all object in a particular schema with a parallelism of 2 use the command: execute utl_recomp.recomp_parallel(2, ‘SCOTT’);

SQL> execute utl_recomp.recomp_parallel(2, 'SCOTT');

PL/SQL procedure successfully completed.

SQL>

This completes the recompilation of objects using the package UTL_RECOMP with subprogram unit RECOMP_PARALLEL.

Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com