11.1.1.1 DBMS_SESSION programs

Table 11.1 lists the programs found in the DBMS_SESSION package.

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

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.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.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.

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.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.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.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.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.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.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.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.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.