1. From within Oracle SQL*Plus session.
2. Using the userenv(‘SESSIONID’) to retrieve current sid and serial#.
select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);
SQL> select sid, serial# from v$session where audsid=userenv(‘SESSIONID’);
SID SERIAL#
———- ———-
408 16328
SQL>
3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.
Larry Catt
OCP
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