#

| Chapter 11 |

# 11. Managing Session Information

Contents:
DBMS_SESSION: Managing Session Information
DBMS_System: Setting Events for Debugging

Oracle technologies allow for a great deal of user customization and security. Language preferences can be specified at the session level using the NLS (National Language Support) options of the ALTER SESSION command. Roles can be used to distinguish groups of users from each other and to modify application behavior accordingly. This chapter describes the following two packages that allow you to perform these operations:

DBMS_SESSION

  Contains programs that can modify and inspect session roles and settings from within PL/SQL. This package also contains programs for manipulating session memory and package states; even if they aren't used very often, these programs are very instructive to understand.

DBMS_SYSTEM

  Lets administrators set various trace events in other users' sessions. Setting these events can be invaluable when tracking down difficult application performance or database issues.

## 11.1 DBMS_SESSION: Managing Session Information

The DBMS_SESSION package lets you access and alter session-level settings, including features of the ALTER SESSION and SET ROLE commands in SQL. Some of the session modifications that can be made using DBMS_SESSION include the following:

- Enabling and disabling roles

- Setting National Language Support (NLS) characteristics

- Resetting package states and releasing session package memory

- Setting Trusted Oracle label characteristics

DBMS_SESSION is used primarily to set preferences and security levels for a user's current database session. For instance, if an application requires specific roles to be set prior to performing specific tasks, this can be done using DBMS_SESSION's SET_ROLE procedure.

Most of the programs in DBMS_SESSION would not likely be called deep in the processing layers of application code, but rather invoked early in the application to establish a necessary, appropriate, or preferred context for the user of the application.

### 11.1.1 Getting Started with DBMS_SESSION

The DBMS_SESSION package is created when the Oracle database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1, Introduction), contains the source code for this package's specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym DBMS_SESSION for the package and grants EXECUTE privilege on the package to public. This way, all Oracle users can reference and make use of this package.

#### 11.1.1.1 DBMS_SESSION programs

Table 11.1 lists the programs found in the DBMS_SESSION package.

Table 11.1: DBMS_SESSION Programs
Name | Description | Use in SQL?
--- | --- | ---
CLOSE_DATABASE_LINK | Closes an inactive but open database link | No
FREE_UNUSED_USER_MEMORY | Releases unused session memory | No
IS_ROLE_ENABLED | Returns TRUE if role enabled | No
RESET_PACKAGE | Clears all persistent package state | No
SET_CLOSE_CACHED_OPEN_CURSORS | Turns automatic closing of cached cursors on or off | No
SET_LABEL | Sets Trusted Oracle label | No
SET_MLS_LABEL_FORMAT | Sets Trusted Oracle MLS label format | No
SET_NLS | Sets National Language Support characteristics for the session | No
SET_ROLE | Enables or disables roles for the session | No
SET_SQL_TRACE | Turns session SQL tracing on or off | No
UNIQUE_SESSION_ID | Returns a unique character string for the session | Yes

DBMS_SESSION does not declare any exceptions of its own or any nonprogram elements.

### 11.1.2 Modifying Session Settings

This section describes the procedures you can use to modify various session settings: SET_LABEL, SET_MLS_LABEL_FORMAT, SET_NLS, and SET_ROLE.

#### 11.1.2.1 The DBMS_SESSION.SET_LABEL procedure

The SET_LABEL procedure sets the session's label used by Trusted Oracle databases. The header looks like this:

>     PROCEDURE DBMS_SESSION.SET_LABEL
>         (lbl IN VARCHAR2);

The lbl parameter contains the label for the session.

#### 11.1.2.1.1 Exceptions

The SET_LABEL procedure does not raise any package exceptions. If Trusted Oracle is not installed, calling SET_LABEL results in the following Oracle exception:

ORA-02248

  Invalid option for ALTER SESSION.

#### 11.1.2.1.2 Restrictions

Note the following restrictions on calling SET_LABEL:

- DBMS_SESSION.SET_LABEL is not allowed in remote sessions.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.2.1.3 Example

This example uses the SET_LABEL procedure to set the session label to DBHIGH:

>     BEGIN
>        DBMS_SESSION.SET_LABEL('DBHIGH');
>     END;

For more information on SET_LABEL parameters, see the Trusted Oracle7 Server Administrator's Guide.

#### 11.1.2.2 DBMS_SESSION.SET_MLS_LABEL_FORMAT procedure

The SET_MLS_LABEL_FORMAT procedure sets the session's default format for the labels used by Trusted Oracle databases.

>     PROCEDURE DBMS_SESSION.SET_MLS_LABEL_FORMAT
>        (fmt IN VARCHAR2);

The fmt parameter is a VARCHAR2 that contains the label format for the session.

The program does not raise any exceptions.

#### 11.1.2.2.1 Restrictions

Note the following restrictions on calling SET_MLS_LABEL_FORMAT:

- DBMS_SESSION.SET_MLS_LABEL_FORMAT is not allowed in remote sessions.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.2.2.2 Example

For more information on MLS label formats, see the Trusted Oracle7 Server Administrator's Guide.

#### 11.1.2.3 The DBMS_SESSION.SET_NLS procedure

The SET_NLS procedure sets or alters National Language Support characteristics for the current session. The SET_NLS header looks like this:

>     PROCEDURE DBMS_SESSION.SET_NLS
>        (param IN VARCHAR2
>        ,value IN VARCHAR2);

Parameters are summarized in the following table.

Parameter | Description
--- | ---
param | NLS parameter being set for the session (see following list of values)
value | Value of the specified parameter

The following session NLS characteristics can be set via this procedure; specify the desired one as the value of the param parameter:

>      NLS_CALENDAR
>      NLS_CURRENCY
>      NLS_DATE_FORMAT
>      NLS_DATE_LANGUAGE
>      NLS_ISO_CURRENCY
>      NLS_LANGUAGE
>      NLS_NUMERIC_CHARACTERS
>      NLS_SORT
>      NLS_SPECIAL_CHARS
>      NLS_TERRITORY

#### 11.1.2.3.1 Exceptions

The DBMS_SESSION.SET_NLS procedure does not raise any exceptions. However, if the value parameter represents a format mask (which would be double-quoted in the ALTER SESSION command) it needs to be enclosed in triple quotes. Otherwise, the following Oracle error will be raised:

ORA-00922

  Missing or invalid option.

#### 11.1.2.3.2 Restrictions

Note the following restrictions on calling this procedure:

- SET_NLS cannot be called from triggers.

- SET_NLS is not allowed in remote sessions.

- The procedure does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.2.3.3 Examples

To change the default date format for the current session, as used by the TO_CHAR and TO_DATE functions, specify the following:

>     BEGIN
>        DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''YYYY:MM:DD''');
>        DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE));
>     END;

The above PL/SQL block produces a result similar to this:

>     1997:07:26

Enclose the NLS parameter name in single quotes if it is passed as a string literal. Another option is to initialize a VARCHAR2 variable to the parameter value and pass the variable name for the param parameter. In this case, the variable name is not quoted in the call to SET_NLS.

As mentioned previously, if the value parameter represents a format mask (which would be double-quoted in the ALTER SESSION command) it needs to be enclosed in triple quotes. Otherwise, the ORA-00092 Oracle error will be raised.

> WARNING: The following sample code and output reveals a problem with the Oracle7.3 DBMS_SESSION.SET_NLS procedure. The NLS_DATE_FORMAT being passed in is valid, yet the procedure will generate an unhandled exception. This problem appears to have been fixed in the Oracle 8.0 package.

>     SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';
>     Session altered.

>     SQL> BEGIN
>       2  DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','"YYYY:MM:DD:HH24:MI:SS"');
>       3  END;
>       4  /
>     BEGIN
>     *
>     ORA-06510: PL/SQL: unhandled user-defined exception
>     ORA-06512: at "SYS.DBMS_SESSION", line 46
>     ORA-06512: at line 2

You can query the NLS settings for the current session from the V$NLS_PARAMETERS virtual table as follows:

>     SELECT parameter, value
>       FROM v$nls_parameters;

See the Oracle7 Server Reference Manual for information on the possible meanings and values for the various NLS settings.

#### 11.1.2.4 The DBMS_SESSION.SET_ROLE procedure

The SET_ROLE procedure enables and disables roles for the current session. It is equivalent to executing the SET ROLE command in SQL. The procedure call simply appends the text of the single parameter, role_cmd, to the string "SET ROLE" and executes the resulting SQL command. The SET_ROLE header looks like this:

>     PROCEDURE DBMS_SESSION.SET_ROLE
>            (role_cmd IN VARCHAR2);

#### 11.1.2.4.1 Exceptions

The program does not raise any package exceptions. It can raise the following Oracle exceptions due to improper data in the role_cmd parameter:

ORA-01919

  Role "rolename" does not exist.

ORA-01979

  Missing or invalid password for role "rolename."

ORA-01924

  Role "rolename" not granted or does not exist.

#### 11.1.2.4.2 Restrictions

Note the following restrictions on calling the SET_ROLE procedure:

- SET_ROLE cannot be called from triggers.

- SET_ROLE cannot be called from PL/SQL stored procedures or functions.

- SET_ROLE does not assert a purity level with the RESTRICT_REFERENCES pragma.

To successfully enable a role, the user must already have been granted the role. After executing the SET_ROLE procedure, only the roles specified in the procedure call will be enabled.

#### 11.1.2.4.3 Examples

Set the password-protected role SPECIAL_ROLE in the current session like this:

>     BEGIN
>        DBMS_SESSION.SET_ROLE('SPECIAL_ROLE IDENTIFIED BY password');
>     END;

Disable all roles in the current session like this:

>     BEGIN
>        DBMS_SESSION.SET_ROLE('NONE');
>     END;

Set multiple roles for the current session like this:

>     BEGIN
>        DBMS_SESSION.SET_ROLE('ROLE1, ROLE2, ROLE3');
>     END;

The role_cmd parameter is case-insensitive, which is the default behavior for role names in the Oracle catalog. To set a case-sensitive role, the role name must be double-quoted in the role_cmd parameter. For example:

>     BEGIN
>        DBMS_SESSION.SET_ROLE('"special_role"');
>     END;

Notice that the lowercase role name special_role is double-quoted and also contained in a string literal delimited by single quotes.

See the Oracle7 Server SQL Reference for more details about the SET ROLE command.

### 11.1.3 Obtaining Session Information

This section describes the functions you can use to obtain information about a session: IS_ROLE_ENABLED and UNIQUE_SESSION_ID.

#### 11.1.3.1 The DBMS_SESSION.IS_ROLE_ENABLED function

The IS_ROLE_ENABLED function returns TRUE or FALSE depending on whether the role specified in the rolename parameter is enabled. The header for this function follows:

>     FUNCTION DBMS_SESSION.IS_ROLE_ENABLED
>        (rolename IN VARCHAR2)
>     RETURN BOOLEAN;

The rolename parameter specifies the name of the role.

The IS_ROLE_ENABLED function does not raise any declared exceptions and does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.3.1.1 Example

The following PL/SQL block will detect whether the user's session currently has the CONNECT role enabled:

>     BEGIN
>        IF DBMS_SESSION.IS_ROLE_ENABLED('CONNECT');
>        THEN
>           DBMS_OUTPUT.PUT_LINE('CONNECT IS ENABLED');
>        END IF;
>     END;

An application might use this function to implement role-dependent behavior as in the following code fragment:

>     IF DBMS_SESSION.IS_ROLE_ENABLED('APP_ADMIN')
>     THEN
>        /*
>        || do special administrative logic here
>        */
>        app_admin_setup;
>     ELSE
>        /*
>        || do the normal user logic here
>        */
>        user_setup;
>     END IF;

> NOTE: The IS_ROLE_ENABLED function is case-sensitive on the rolename parameter, so beware of unexpected results. The default behavior is for role names to be uppercase in the Oracle catalog, so it is probably best to cast the rolename to uppercase prior to calling this function.

For example, in the following block, the variable return_TF will be set to FALSE when CONNECT is enabled for the session because the CONNECT role is uppercase in the catalog:

>     DECLARE
>        return_TF   BOOLEAN;
>     BEGIN
>        return_TF := DBMS_SESSION.IS_ROLE_ENABLED('connect');
>     END;

#### 11.1.3.2 The DBMS_SESSION.UNIQUE_SESSION_ID function

The UNIQUE_SESSION_ID function returns a character string unique to the session among all sessions currently connected to the database. The return string can be up to 24 bytes in length. Multiple calls to the function from the same session will always return the same string. The program header follows:

>     FUNCTION DBMS_SESSION.UNIQUE_SESSION_ID
>     RETURN VARCHAR2;

The UNIQUE_SESSION_ID function does not raise any exceptions.

#### 11.1.3.2.1 Restrictions

Prior to Oracle 7.3.3, this function did not assert a purity level with the RESTRICT_REFERENCES pragma. In Oracle 7.3.3 and above, the program asserts the following purity:

>     PRAGMA RESTRICT_REFERENCES(UNIQUE_SESSION_ID,WNDS,RNDS,WNPS);

Thus, you can call the UNIQUE_SESSION_ID function directly in SQL for Oracle databases at release 7.3.3 and later.

#### 11.1.3.2.2 Example

To display the value of the unique id for the current session, specify the following:

>     DECLARE
>        my_unique_id   VARCHAR2(30);
>     BEGIN
>        my_unique_id := DBMS_SESSION.UNIQUE_SESSION_ID;
>        DBMS_OUTPUT.PUT_LINE('UNIQUE ID: '||my_unique_id);
>     END;

An example of output from executing the preceding PL/SQL block follows:

>     UNIQUE ID: F000E4020000

UNIQUE_SESSION_ID is functionally identical to the DBMS_PIPE.UNIQUE_SESSION_NAME function; however, their return values are not identical. Be very careful not to write code that assumes that these two functions are equivalent.

> NOTE: Ace technical reviewer Phil Pitha points out that the two functions may be related. He writes that it appears that DBMS_PIPE.UNIQUE_SESSION_NAME returns an id of the format: `ORA$PIPE$' || DBMS_SESSION.UNIQUE_SESSION_ID.

### 11.1.4 Managing Session Resources

This section describes the procedures you can use to manage your session resources: CLOSE_DATABASE_LINK, FREE_UNUSED_USER_MEMORY, RESET_PACKAGE, SET_CLOSE_CACHED_OPEN_CURSORS, and SET_SQL_TRACE.

#### 11.1.4.1 The DBMS_SESSION.CLOSE_DATABASE_LINK procedure

The CLOSE_DATABASE_LINK procedure is used to close an open but inactive database link in the session. The header for the program is:

>     PROCEDURE DBMS_SESSION.CLOSE_DATABASE_LINK
>        (dblink IN VARCHAR2);

where dblink specifies the name of the database link.

#### 11.1.4.1.1 Exceptions

The CLOSE_DATABASE_LINK procedure does not raise any package exceptions. It can raise the following Oracle exceptions when the referenced database link cannot be closed:

ORA-02080

  Database link is in use.

ORA-02081

  Database link is not open.

#### 11.1.4.1.2 Restrictions

Note the following restrictions on calling CLOSE_DATABASE_LINK:

- All cursors using the database link must be closed, and any transactions that reference the link (for UPDATE or SELECT purposes) must be ended (with COMMIT or ROLLBACK).

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.4.1.3 Example

To close a database link named LOOPBACK, specify the following:

>     BEGIN
>        DBMS_SESSION.CLOSE_DATABASE_LINK('LOOPBACK');
>     END;

Use of database links establishes a proxy session for the local user on the remote database, and this is a relatively expensive process. This is why Oracle keeps database links open rather than closing them immediately upon completion of the remote operation. Therefore, the CLOSE_DATABASE_LINK procedure probably should not be routinely called, especially for database links that are likely to be referenced again in the current session.

#### 11.1.4.2 The DBMS_SESSION.FREE_UNUSED_USER_MEMORY procedure

The FREE_UNUSED_USER_MEMORY procedure is used to reclaim user session memory, especially when memory-intensive operations have been performed in the session. The header for this procedure follows:

>     PROCEDURE DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

The FREE_UNUSED_USER_MEMORY procedure does not raise any exceptions and does not assert a purity level with the RESTRICT_REFERENCES pragma.

Oracle Corporation recommends that this procedure be used only when memory is at a premium and the session has allocated large (greater than 100 Kb) amounts. This caution indicates that calling the FREE_UNUSED_USER_MEMORY procedure is itself a relatively expensive operation and should be done only when really necessary. Calling the procedure forces Oracle to deallocate any session memory previously allocated but currently unused.

The following examples are the kinds of operations that can lead to large memory allocations:

- In-memory sorts where the SORT_AREA_SIZE initialization parameter is large

- Compilation of large PL/SQL packages, procedures, and functions

- Use of PL/SQL tables to store large amounts of data

In each of these cases, memory allocated for a specific purpose can only be re-used by Oracle for that same purpose. For example, memory allocated to the session for large PL/SQL compilations will not be reused later for anything but compilation of PL/SQL. If no more compilation is anticipated and memory is scarce, this is a good time to call FREE_UNUSED_USER_MEMORY.

In the case of PL/SQL tables, the scope of the table determines when memory allocated to the table is no longer in use and can be freed. Memory allocated for tables declared locally by procedures and functions becomes unused once the module completes execution. Other PL/SQL tables (local or global) can then make use of this memory. However, the memory remains allocated to the session. Global tables (i.e., tables declared at the package level) have persistent scope, and the memory associated with them can be made reusable only by assigning a NULL table to them. In either case, once the memory is made reusable, it is also eligible to be freed (deallocated from the session) using the FREE_UNUSED_USER_MEMORY procedure.

Figure 11.1 illustrates the relationship between session memory and local versus global PL/SQL tables. In the figure, after package1.procedure1 executes and FREE_UNUSED_USER_MEMORY is called, the memory for PL/SQL table2 is still allocated to the session. On the other hand, package2.procedure1 declares both tables locally, so that all the memory they use can be freed after the procedure completes.

#### Figure 11.1: Package architecture and persistent session memory

Memory freed by calling this procedure is returned to either the operating system or the Oracle shared pool. This depends on the Oracle configuration and session connection as follows:

- Sessions connected through multithreaded servers return memory to the shared pool.

- Sessions with dedicated server connections return memory to the operating system.

Session memory can be monitored through the statistics for "session uga memory" and "session pga memory" found in the V$SESSTAT virtual table.

See the DBMS_SESSION package specification in the dbmsutil.sql file for more information on session memory and the effects of the FREE_UNUSED_USER_MEMORY procedure.

#### 11.1.4.3 The DBMS_SESSION.RESET_PACKAGE procedure

The RESET_PACKAGE procedure resets all package states for the session. The values of any persistent package variables will be lost after execution of this procedure. The header for this procedure follows:

>     PROCEDURE DBMS_SESSION.RESET_PACKAGE;

The RESET_PACKAGE procedure does not raise any exceptions.

#### 11.1.4.3.1 Restrictions

Note the following restrictions on calling RESET_PACKAGE:

- Use the RESET_PACKAGE procedure with great caution, as it will cause the loss of package state in all packages for the current session. Applications making use of persistent package variables may be compromised and fail to work properly after calls to RESET_PACKAGE. Therefore, in general, don't embed calls to this procedure in application programs.

- The program does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.4.3.2 Example

The output buffer used by DBMS_OUTPUT is actually a global package data structure that is initialized when either DBMS_OUTPUT.ENABLE or SET SERVEROUTPUT ON is called in PL/SQL or SQL*Plus, respectively.

The following PL/SQL block will not display the message, because the call to RESET_PACKAGE clears out the DBMS_OUTPUT buffer:

>     BEGIN
>        DBMS_SESSION.RESET_PACKAGE;
>        DBMS_OUTPUT.PUT_LINE('This is an invisible message');
>     END;

Although calling RESET_PACKAGE will clear all persistent package variables, including PL/SQL tables and records, it will not automatically release the session memory used by these structures. To release session memory back to the operating system (or Oracle shared pool), use the FREE_UNUSED_USER_MEMORY procedure.

Note that package states cannot be reinstantiated until the outermost PL/SQL calling scope within which RESET_PACKAGE was called completes. This variation on the previous example illustrates the effect of RESET_PACKAGE within its calling scope:

>     /* Filename on companion disk: sess1.sql */*
>     set serveroutput on size 1000000
>     BEGIN
>        DBMS_SESSION.RESET_PACKAGE;
>        DBMS_OUTPUT.PUT_LINE('You will not see this');
>        DBMS_OUTPUT.ENABLE;
>        DBMS_OUTPUT.PUT_LINE('Also invisible, since in same scope '||
>                             'as RESET_PACKAGE call');
>     END;
>     /
>     set serveroutput on size 1000000
>     BEGIN
>        DBMS_OUTPUT.PUT_LINE('New package states instantiated '||
>                             'messages visible again!');
>     END;
>     /

Output from running this script follows:

>     SQL> @c:\opbip\examples\sess1.sql

>     PL/SQL procedure successfully completed.

>     New package states instantiated messages visible again!

>     PL/SQL procedure successfully completed.

As you can see, only the final call to DBMS_OUTPUT.PUT_LINE displays its message. The second call to DBMS_OUTPUT.PUT_LINE in the first block fails to produce output because the buffer used by DBMS_OUTPUT cannot be reinitialized within the same calling scope as RESET_PACKAGE.

#### 11.1.4.4 The DBMS_SESSION.SET_CLOSE_CACHED_OPEN_CURSORS procedure

The SET_CLOSE_CACHED_OPEN_CURSORS procedure is used to set the close_cached_open_cursors property at the session level, overriding the database-wide setting established by the CLOSE_CACHED_OPEN_CURSORS initialization parameter. The header for this procedure looks like this:

>     PROCEDURE DBMS_SESSION.SET_CLOSE_CACHED_OPEN_CURSORS
>        (close_cursors IN BOOLEAN);

The close_cursors parameter causes cached PL/SQL cursors to be automatically closed with a value of TRUE or kept open with a value of FALSE.

The SET_CLOSE_CACHED_OPEN_CURSORS procedure does not raise any declared exceptions and does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.4.4.1 Example

To set CLOSE_CACHED_OPEN_CURSORS to on for the current session, specify the following:

>     BEGIN
>        DBMS_SESSION.SET_CLOSE_CACHED_OPEN_CURSORS(TRUE);
>     END;

Most applications will probably have no need to use this procedure. When set to TRUE, the Oracle database will automatically close any cached PL/SQL cursors after a COMMIT or ROLLBACK, which releases the memory used by these cursors. When set to FALSE, cursors are held open in the cache across transactions, making subsequent executions somewhat faster. Applications that tend to use large cursors in a one-time or infrequent fashion (e.g., ad hoc query systems against a data warehouse) may benefit from setting this value to TRUE.

See the Oracle7 Server Reference Manual for more information on the CLOSE_CACHED_OPEN_CURSORS initialization parameter.

#### 11.1.4.5 The DBMS_SESSION.SET_SQL_TRACE procedure

The SET_SQL_TRACE procedure is equivalent to the ALTER SESSION SET SQL_TRACE command. It is used to turn the Oracle SQL trace facility on or off for the session, primarily while debugging application problems. The SET_SQL_TRACE header looks like this:

>     PROCEDURE DBMS_SESSION.SET_SQL_TRACE
>         (sql_trace IN BOOLEAN);

The sql_trace parameter sets the trace on if TRUE, off if FALSE.

The SET_SQL_TRACE procedure does not raise any exceptions.

#### 11.1.4.5.1 Restrictions

Note the following restrictions on calling SET_SQL_TRACE:

- In general, use the SET_SQL_TRACE procedure only when debugging application problems. Tracing session SQL calls adds overhead to the database and can generate numerous and sizable trace files on the host server.

- The SET_SQL_TRACE procedure does not assert a purity level with the RESTRICT_REFERENCES pragma.

#### 11.1.4.5.2 Example

Generate a trace file for the execution of a specific PL/SQL procedure call like this:

>     BEGIN
>        DBMS_SESSION.SET_SQL_TRACE(TRUE);
>        plsql_procedure_call;
>        DBMS_SESSION.SET_SQL_TRACE(FALSE);
>     END;

The trace files generated when SQL tracing is turned on are created in the directory specified by the USER_DUMP_DEST initialization parameter for the Oracle database to which the session is connected.

Trace file naming conventions often make it difficult to identify the correct trace file when there are many in the directory specified by USER_DUMP_DEST. Executing a "tag" SQL statement after turning tracing on can facilitate trace file identification.

The trace files generated when SQL tracing is turned on are not directly readable. The Oracle utility program TKPROF can be used to generate a formatted summary of the trace file contents. The TKPROF output contains statistics on CPU time, elapsed time, and disk reads for the parse, execute, and fetch steps of each SQL statement in the trace file. This information can be invaluable when tracking down performance problems in complex applications. See the Oracle7 Server Tuning manual for instructions on using TKPROF.

### 11.1.5 DBMS_SESSION Examples

The DBMS_SESSION package is a kind of grab bag of ways to alter the user's current session characteristics in an Oracle database. Oracle provides a great deal of flexibility with respect to language settings and security. DBMS_SESSION includes several programs that are equivalent to the SQL commands SET ROLE and ALTER SESSION normally used to establish these settings. The following programs in DBMS_SESSION fall into this category:

SET_LABEL
SET_MLS_LABEL_FORMAT
SET_NLS
SET_ROLE

These programs are "high-level," in that they would normally be called directly from an application program and not be buried deep inside layers of PL/SQL code. In fact, the SET_ROLE procedure can only be called from anonymous PL/SQL blocks and not from within stored program (procedures and functions) code. So in practice, an application would begin by prompting the user for preferences, issue the appropriate DBMS_SESSION.SET procedure calls, and then move on to the real work.

Other programs in DBMS_SESSION are geared toward manipulating session-level resource utilization, particularly memory. In this category are the following DBMS_SESSION programs:

SET_CLOSE_CACHED_OPEN_CURSORS
CLOSE_DATABASE_LINK
FREE_UNUSED_USER_MEMORY
RESET_PACKAGE

These are also quite "high-level" routines, but more likely to find their way into application code under the right circumstances.

One thing that DBMS_SESSION does not have (and that it should) is a function to return the current session id. This is frequently asked for by developers and DBAs and is relatively easy to provide.

> NOTE: The source code for all of the examples is in a file called mysess.sql, which creates the package called my_session shown in this section that includes these examples.

#### 11.1.5.1 Adding value to DBMS_SESSION

Let's take a look at how we can use DBMS_SESSION and add a little value along the way. I've created a package called my_session to do just that. Here is the package specification:

>     /* Filename on companion disk: mysess.sql */*
>     CREATE OR REPLACE PACKAGE my_session
>        /*
>        || Extends some of the functionality of DBMS_SESSION
>        || and provides access to additional session-level
>        || information.
>        ||
>        || Author:  John Beresniewicz, Savant Corp
>        ||
>        || 12/22/97: exposed load_unique_id as/per Phil Pitha
>        || 09/07/97: modified function SID to assert WNPS
>        ||           and not call load_my_session_rec
>        || 07/27/97: created
>        ||
>        || Compilation Requirements:
>        ||
>        || SELECT on sys.v_$session
>        || SELECT on sys.v_$sesstat
>        || SELECT on sys.v_$statname
>        ||
>        || Execution Requirements:
>        ||
>        || ALTER SESSION
>        */
>     AS
>        /* same as DBMS_SESSION.UNIQUE_SESSION_ID but callable in SQL */
>        FUNCTION unique_id RETURN VARCHAR2;
>        PRAGMA RESTRICT_REFERENCES(unique_id, WNDS,WNPS);

>        /*
>        || loads unique_session_id into global variable, must be called
>        || prior to using function unique_id
>        */
>        PROCEDURE load_unique_id;

>        /* returns session id of current session, callable in SQL */
>        FUNCTION sid RETURN NUMBER;
>        PRAGMA RESTRICT_REFERENCES(sid,WNDS,WNPS);

>        /* closes any open database links not in use */
>        PROCEDURE close_links
>           (force_with_commit_TF IN BOOLEAN DEFAULT FALSE);

>        /* loads session data, should be private but needs to assert purity */
>        PROCEDURE load_my_session_rec;
>        PRAGMA RESTRICT_REFERENCES(load_my_session_rec,WNDS);

>        /* resets package states and frees memory */
>        PROCEDURE reset;

>        /* returns current stat value from V$SESSTAT for this session */
>        FUNCTION statval(statname_IN IN VARCHAR2) RETURN NUMBER;

>        /* displays session uga and pga using DBMS_OUTPUT */
>        PROCEDURE memory;

>        /* turns SQL tracing on/off with tag for file identification */
>        PROCEDURE set_sql_trace
>           (trace_TF IN  BOOLEAN
>           ,tag_IN  IN  VARCHAR2 DEFAULT USER);

>     END my_session;

You will notice that several of the programs seem very similar to programs in DBMS_SESSION. Well, they are, but with some important differences.

#### 11.1.5.2 The unique_id function

Prior to Oracle 7.3.3, the DBMS.SESSION.UNIQUE_SESSION_ID function did not assert any purity level using the RESTRICT_REFERENCES pragma and thus could not be called directly from SQL statements. This is unfortunate, because one nice potential use of the function is as an identifier for applications making use of shared temporary tables. In other words, some applications will find it useful to do things like the following:

>     INSERT INTO temp_table (session_id, other_columns...)
>     VALUES (DBMS_SESSION.UNIQUE_SESSION_ID, other_columns...);

>     DELETE FROM temp_table
>      WHERE session_id = DBMS_SESSION.UNIQUE_SESSION_ID;

Thankfully, Oracle Corporation has corrected this shortcoming in the latest releases of DBMS_SESSION. For those not fortunate enough to be using 7.3.3 or 8.0, the my_session.unique_id function can be used as a workaround. This function returns the same string as DBMS_SESSION.UNIQUE_SESSION_ID, yet it asserts a purity level of WNDS and can thus be called from SQL.

Here is the source to unique_id and its companion procedure load_unique_id:

>     /* Filename on companion disk: mysess.sql */

>     /* private global to hold DBMS_SESSION.UNIQUE_SESSION_ID */
>        unique_id_  VARCHAR2(40);

>        /*
>        || loads unique_session_id into global variable, must be called
>        || prior to using function unique_id
>        */
>        PROCEDURE load_unique_id
>        IS
>        BEGIN
>     	unique_id_ := DBMS_SESSION.UNIQUE_SESSION_ID;
>        END load_unique_id;

>        /*
>        || returns unique_id_ loaded by call to load_unique_id
>        */
>        FUNCTION unique_id RETURN VARCHAR2
>        IS

>        BEGIN
>           RETURN unique_id_;
>        END unique_id;

As you can see, unique_id simply returns the value of a private package global variable that is set by the load_unique_id procedure to the value returned by DBMS_SESSION.UNIQUE_SESSION_ID. The only caveat is that load_unique_id must be called in the session prior to calling unique_id (or a NULL value will be returned). Note that using a private global and function is safer than using a public global, since the public global cannot be protected from inadvertent modification.

#### 11.1.5.3 The load_my_session_rec procedure

Each session has a unique row in the V$SESSION virtual table with various columns containing identification and activity information about the session. The load_my_session_rec procedure selects the row in V$SESSION corresponding to the current session and loads it into a package global record called my_session_rec.

>     /* Filename on companion disk: mysess.sql */*
>     /*
>     || my_session_cur and my_session_rec are both declared
>     || to always hold all columns of V$SESSION
>     */
>     CURSOR my_session_cur
>     IS
>     SELECT  *
>     	FROM  sys.v_$session
>     	WHERE  audsid = USERENV('SESSIONID');

>     my_session_rec  sys.v_$session%ROWTYPE;

>     /*
>     || loads V$SESSION data into global record for current session
>     */
>     PROCEDURE load_my_session_rec
>     IS
>     BEGIN
>     	OPEN my_session_cur;
>     	FETCH my_session_cur INTO my_session_rec;
>     	CLOSE my_session_cur;
>     END load_my_session_rec;

Notice that load_my_session_rec is written in a way that ensures it always gets all columns of V$SESSION. This is accomplished by anchoring the package global my_session_rec to V$SESSION using %ROWTYPE in the declaration. Similarly, the cursor my_session_cur used to fetch into my_session_rec is anchored to V$SESSION by using the SELECT * syntax. This is a nice technique. Since V$SESSION can change with Oracle versions, writing the procedure in this way allows it to adjust itself to the particular version of Oracle under which it is executing.

#### 11.1.5.4 The sid function

Several of the Oracle dynamic performance (V$) views are keyed by session id because they contain session-level performance data. Many developers and DBAs have had to answer the question "What is my current sid?" when delving into these performance tables. I don't know why DBMS_SESSION does not come with a sid function, but my_session sure does. Here is the relevant source code:

>     /* Filename on companion disk: mysess.sql */   /*
>     /*
>     	|| returns the session id of current session
>     */
>     FUNCTION sid RETURN NUMBER
>     	temp_session_rec sys.v_$session%ROWTYPE;
>     BEGIN
>     	IF my_session_rec.sid IS NULL
>     	THEN
>     		OPEN my_session_cur;
>     		FETCH my_session_cur INTO temp_session_rec;
>     		CLOSE my_session_cur;
>     	ELSE
>     		temp_session_rec := my_session_rec;
>           END IF;
>           RETURN temp_session_rec.sid;
>     END sid;

The sid function itself is quite simple, yet it has a subtle but important performance optimization. Since the session id will never change for the duration of the session, it is necessary to load it only once, and this can be done using the load_my_session_rec procedure. The IF statement checks to see if we've already loaded the my_session_rec.sid and bypasses opening my_session_cur in that case. Remember that we intend to use the function in SQL statements, and it will be executed for every row returned in which the function is referenced. That simple IF statement could save hundreds (or even thousands) of scans on the V$SESSION view per SQL statement. Be sure to execute load_my_session_rec before using the sid function to avoid the unnecessary performance penalty.

We can use the my_session.sid function to view our current performance statistics from V$SESSTAT as follows:

>     SELECT n.name,s.value
>       FROM v$statname n, v$sesstat s
>      WHERE n.statistic# = s.statistic#
>        AND s.sid = my_session.sid;

> NOTE: Astute readers may ask: Why not just call load_my_session_rec from the sid function if my_session_rec.sid has not been initialized? Well, originally this is exactly the way sid was written. However, since load_my_session_rec modifies package state, this meant that WNPS (Writes No Package State) purity could not be asserted for the sid function. In order to use a PL/SQL function in the WHERE clause of a SQL statement, the function must assert WNPS, so sid had to be modified to not call load_my_session_rec.

#### 11.1.5.5 The close_links procedure

The Oracle initialization parameter OPEN_LINKS controls the maximum number of concurrent open connections to remote databases per user session. When a session exceeds this number, the following Oracle error is raised:

ORA-02020

  Too many database links in use.

Generally, the database administrator should set the OPEN_LINKS parameter to a value that will accommodate the needs of distributed applications accessing the database. However, in highly distributed environments with multiple applications, it's possible that users could receive the ORA-02020 error. Presumably, this is the purpose of the CLOSE_DATABASE_LINK procedure; however, there is a serious problem. Quite simply, users should not have to know anything about database links nor, for that matter, should applications. Database links are an implementation detail that should be kept transparent to users and applications. The real question is: When an ORA-02020 is incurred, how is a user or application supposed to know which links are open and can be closed?

Well, it's my opinion that users should not have to know about specific database links and yet should be able to do something in case of an ORA-02020 error. That is precisely the purpose of my_session.close_links. That procedure will close any open links that are not in use and can be closed. A link is considered in use if it has been referenced within the current transaction (i.e., since the last COMMIT or ROLLBACK). Alternatively, close_links will close all open links by issuing a COMMIT to terminate the current transaction and free all links for closure.

>     /* Filename on companion disk: mysess.sql */*
>     /*
>     || closes all open database links not in use by session,
>     || or all if forced_with_commit_TF is TRUE
>     */
>     PROCEDURE close_links
>        (force_with_commit_TF IN BOOLEAN DEFAULT FALSE)

>        IS

>           /* declare exceptions for ORA errors */
>           dblink_in_use EXCEPTION;
>           PRAGMA EXCEPTION_INIT(dblink_in_use,-2080);

>           dblink_not_open EXCEPTION;
>           PRAGMA EXCEPTION_INIT(dblink_not_open,-2081);

>           /* cursor of all db links available to user */
>           CURSOR all_links_cur
>           IS
>           SELECT db_link
>             FROM all_db_links;

>        BEGIN
>           /* try all links and close the ones you can */
>           FOR dblink_rec IN all_links_cur
>           LOOP
>              BEGIN
>                 DBMS_SESSION.CLOSE_DATABASE_LINK(dblink_rec.db_link);
>              EXCEPTION
>                 WHEN dblink_not_open
>                    THEN null;
>                 WHEN dblink_in_use
>                    THEN
>                       IF force_with_commit_TF
>                       THEN
>                          COMMIT;
>                          DBMS_SESSION.CLOSE_DATABASE_LINK(dblink_rec.db_link);
>                       END IF;
>                 WHEN OTHERS
>                    THEN null;
>              END;
>           END LOOP;
>        END close_links;

There are a few things to note in this procedure. First, exceptions are declared and assigned to the two Oracle errors that can be raised by the DBMS_SESSION.CLOSE_DATABASE_LINK procedure. This is done using PRAGMA EXCEPTION_INIT compiler directives. Next comes a loop through all database links available to the user. For each link in the loop, we execute DBMS_SESSION.CLOSE_DATABASE_LINK in a BEGIN...END block and trap the exceptions raised by links that were not open or in use. Trapping the exceptions allows the loop to continue until all links have been processed.

Originally, the procedure would close only links that were not in use. I decided to enhance it to accept a BOOLEAN parameter called force_with_commit_TF. When this parameter is TRUE, the dblink_in_use exception handler issues a COMMIT. This terminates the current transaction and frees all database links to be closed, including the one that raised the exception that is closed in the exception handler. This enhancement allows the procedure to close all database links for the session.

> NOTE: The V$DBLINK virtual table shows database links that the current session has open. The IN_TRANSACTION column indicates whether the open link is in use or can be closed using DBMS_SESSION.CLOSE_DATABASE_LINK.

#### 11.1.5.6 The set_sql_trace procedure

The SQL trace facility is an invaluable tool for debugging application performance problems. However, one problem that developers and DBAs often run into when using SQL trace is identifying the correct trace file from among the possibly hundreds of trace files that tend to collect and hang around in the directory specified by the USER_DUMP_DEST parameter. One technique is to put a literal tag in trace files by executing a SQL command such as the following:

>     SELECT 'JOHN B: TRACE 1' FROM DUAL;

When issued immediately after setting SQL_TRACE to TRUE, the statement will appear in the trace file, and a utility like grep or awk can be used to scan the directory for the file with the correct literal tag. In my_session.set_sql_trace, I've enhanced DBMS_SESSION.SET_SQL_TRACE to accept a string tag and place it into the trace file when turning trace on. The DBMS_SQL package is used to build and parse a SQL statement dynamically with the tag literal in it.

>     /* Filename on companion disk: mysess.sql */*
>     /*
>     || turns SQL tracing on/off with tag for file identification
>     */
>     PROCEDURE set_sql_trace
>     	(trace_TF IN  BOOLEAN
>     	,tag_IN  IN  VARCHAR2 DEFAULT USER)
>     IS
>     	cursor_id  INTEGER;
>     BEGIN
>     	DBMS_SESSION.SET_SQL_TRACE(trace_TF);

>     	IF trace_TF
>     	THEN
>     		cursor_id := DBMS_SQL.OPEN_CURSOR;

>     		/* parse a SQL stmt with the tag in it */
>     		DBMS_SQL.PARSE
>     			(cursor_id
>     			,'SELECT '''||tag_IN||''' FROM DUAL'
>     			,DBMS_SQL.native);

>     	DBMS_SQL.CLOSE_CURSOR(cursor_id);

>           END IF;
>        END set_sql_trace;

Note that it is not necessary to execute the tag SQL statement; the parse will get it into the trace file. After all, there is no need to do more work than absolutely required.

#### 11.1.5.7 The reset procedure

The DBMS_SESSION.RESET_PACKAGE procedure invalidates all package states, including all global variables and PL/SQL tables. However, it does not free the memory associated with these now empty structures; that is the job of DBMS_SESSION.FREE_UNUSED_USER_MEMORY. The my_session.reset procedure combines these into a single call.

>     /* Filename on companion disk: mysess.sql */*
>     /*
>     || resets all package states and frees memory
>     */
>     PROCEDURE reset
>     IS
>     BEGIN
>     	DBMS_SESSION.RESET_PACKAGE;
>     	DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
>     END reset;

Originally, I designed the reset procedure to call load_unique_id and load_my_session_rec immediately after initializing the package and freeing memory. The idea was that some package states should always be available, so why not reinitialize them immediately? However, I had stepped into the DBMS_SESSION.RESET_PACKAGE trap, which prevents any package state from being established within the same calling scope as the call to RESET_PACKAGE.

It is good practice for programs that rely on package state to check expected package variables and initialize them if necessary.

#### 11.1.5.8 The memory procedure

The my_session.memory procedure was developed to provide experimental results from using DBMS_SESSION.RESET and DBMS_SESSION.FREE_UNUSED_USER_MEMORY. It uses DBMS_OUTPUT to display the current session memory's UGA and PGA sizes. If you ever wondered how much memory that big package really uses, check it out with my_session.memory.

>     /* Filename on companion disk: mysess.sql */*
>     /*
>     || displays session uga and pga using DBMS_OUTPUT
>     */
>     PROCEDURE memory
>     IS
>     BEGIN
>     	DBMS_OUTPUT.ENABLE;
>     	DBMS_OUTPUT.PUT_LINE('session UGA: '||
>     		TO_CHAR(my_session.statval('session uga memory') ) );
>     	DBMS_OUTPUT.PUT_LINE('session PGA: '||
>     		TO_CHAR(my_session.statval('session pga memory') ) );
>     END memory;

The memory procedure uses a function called statval, which returns the value of a V$SESSTAT statistic for the current session by name. It's a handy little function.

>     /* Filename on companion disk: mysess.sql */*
>     /*
>     || returns current value of a statistic from
>     || V$SESSTAT for this session
>     */
>     FUNCTION statval(statname_IN IN VARCHAR2) RETURN NUMBER
>     IS
>     	CURSOR sesstat_cur (statname VARCHAR2)
>     	IS
>     		SELECT  s.value
>       			FROM  sys.v_$sesstat  s
>      				,sys.v_$statname n
>     		WHERE  s.statistic# = n.statistic#
>     			AND  s.sid  = my_session.sid
>     			AND  n.name = statname;
>     	return_temp  NUMBER;
>     BEGIN
>     	OPEN sesstat_cur(statname_IN);
>     	FETCH sesstat_cur INTO return_temp;
>     	CLOSE sesstat_cur;
>     	RETURN return_temp;
>     EXCEPTION
>     	WHEN OTHERS THEN
>     		IF sesstat_cur%ISOPEN
>     		THEN
>     			CLOSE sesstat_cur;
>     		END IF;
>     		RETURN NULL;
>     END statval;

Notice that statval uses the my_session.sid funtion in the cursor sesstat_cur.

The following script demonstrates the inefficiency of PL/SQL tables of VARCHAR2 under Oracle 7.3 using my_session.memory:

>     /* Filename on companion disk: sess2.sql */*
>     set serveroutput on size 100000

>     DECLARE
>     	TYPE my_tabtype IS TABLE OF VARCHAR2(2000)
>     		INDEX BY BINARY_INTEGER;

>     	my_tab my_tabtype;
>     BEGIN
>     	my_session.memory;
>     	FOR i IN 1..1000
>     	LOOP
>     		my_tab(i) := TO_CHAR(i);
>     	END LOOP;
>     	my_session.memory;
>     END;
>     /

Here is sample output from executing this script:

>     session UGA: 36048
>     session PGA: 103328
>     session UGA: 36048
>     session PGA: 2248352

>     PL/SQL procedure successfully completed.

Even though each entry in the PL/SQL table my_tab has at most three characters, session PGA memory grew by more than two megabytes! Luckily, this problem is fixed in PL/SQL8.

III. Server Management Packages |  | 11.2 DBMS_System: Setting Events for Debugging

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.