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:
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.
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.
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.
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.
Table 11.1 lists the programs found in the DBMS_SESSION package.
Use in SQL?
Closes an inactive but open database link
Releases unused session memory
Returns TRUE if role enabled
Clears all persistent package state
Turns automatic closing of cached cursors on or off
Sets Trusted Oracle label
Sets Trusted Oracle MLS label format
Sets National Language Support characteristics for the session
Enables or disables roles for the session
Turns session SQL tracing on or off
Returns a unique character string for the session
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.
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:
Invalid option for ALTER SESSION.
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.
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.
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.
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.
PROCEDURE DBMS_SESSION.SET_NLS (param IN VARCHAR2 ,value IN VARCHAR2);
Parameters are summarized in the following table.
NLS parameter being set for the session (see following list of values)
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:
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:
Missing or invalid option.
Note the following restrictions on calling this procedure:
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:
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;
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);
The program does not raise any package exceptions. It can raise the following Oracle exceptions due to improper data in the role_cmd parameter:
Role "rolename" does not exist.
Missing or invalid password for role "rolename."
Role "rolename" not granted or does not exist.
Note the following restrictions on calling the SET_ROLE procedure:
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.
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.
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.
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.
DECLARE return_TF BOOLEAN; BEGIN return_TF := DBMS_SESSION.IS_ROLE_ENABLED('connect'); END;
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.
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:
Thus, you can call the UNIQUE_SESSION_ID function directly in SQL for Oracle databases at release 7.3.3 and later.
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.
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.
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:
Database link is in use.
Database link is not open.
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.
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.
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.
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 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.
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:
The RESET_PACKAGE procedure does not raise any exceptions.
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.
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.
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.
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.
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.
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.
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.
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:
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:
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.
/* 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.
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.
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.
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.
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.
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:
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.
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;
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.
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.
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:
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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.