Tag Archives: serial

Current sid and serial# of a session from SQL*PLUS

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’);

———- ———-
408 16328


3. Note: That logging out of SQL*Plus session or reconnecting will change your SID and SERIAL#.

Larry Catt

Oracle UTL_RECOMP package and the RECOMP_SERIAL subprogram

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 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 - 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 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options


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.


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.


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

Larry J. Catt, OCP 9i, 10g