The
dbms_session package provides
a number of useful procedures and functions
related to managing and/or controlling
sessions. Begin by breaking down those
offerings along related lines of usefulness
starting with the general purpose ones for
simply getting or setting session level
attributes or characteristics. They are all
fairly straightforward and simple, so no
examples are given for them.
—- Returns a unique id
for a session
DBMS_SESSION.UNIQUE_SESSION_ID RETURN
VARCHAR2;
—- Returns whether
current session is still active
DBMS_SESSION.IS_SESSION_ALIVE (uniqueid
VARCHAR2) RETURN BOOLEAN;
—- Returns whether
named role is enabled for session
DBMS_SESSION.IS_ROLE_ENABLED (rolename
VARCHAR2) RETURN BOOLEAN;
—- Enables and
disables named role for session
—- Same as SQL
command: SET ROLE
DBMS_SESSION.SET_ROLE
(role_cmd VARCHAR2);
—- Permits setting
session's various globalization (NLS)
settings
—- Same as SQL
command: ALTER SESSION SET nls_parameter
= value
DBMS_SESSION.SET_NLS (param
VARCHAR2, value VARCHAR2);
—- Permits setting
session trace flag on or off
—- Same as SQL
command: ALTER SESSION SET SQL_TRACE =
boolean
DBMS_SESSION.SET_SQL_TRACE (sql_trace
boolean);
—- Permits closing an
open database link
—- Same as SQL
command: ALTER SESSION CLOSE DATABSE
LINK dblink_name
DBMS_SESSION.CLOSE_DATABASE_LINK (dblink
VARCHAR2);
—- Frees up unused
memory after large operations (> 100K)
DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
—- Permits session to
de-instantiate (i.e. unload from memory)
-- all packages and
their memory, cursors, global variables,
etc
DBMS_SESSION.RESET_PACKAGE;
—- Permits session to
change the current resource consumer
group
DBMS_SESSION.switch_current_consumer_group
(
new_consumer_group
IN
VARCHAR2,
old_consumer_group
OUT VARCHAR2,
initial_group_on_error
IN
BOOLEAN);
The remaining procedures and functions
primarily support row level security (RLS),
also referred to as virtual private
databases (VPD) and fine grained access
control. Examples of using these functions
are shown in the section on row level
security, so identify their names, purpose,
parameters and defaults.
Set_identifier,
clear_set_identifier and
clear_identifier procedures
permit setting and clearing the client ID
for the session. The client ID of a session
is used to map it to some corresponding
global application context, which is
necessary for RLS and/or VPD.
—- Permits setting the
session application-specific identtifier
DBMS_SESSION.SET_IDENTIFIER (client_id
VARCHAR2);
—- Permits clearing
the session application-specific
identtifier
DBMS_SESSION.CLEAR_IDENTIFIER;
Now move onto procedures and functions
specifically for managing contexts for a
session. These are a little more complicated
and the following data type needs to be
worked with when listing a session's active
contexts:
TYPE AppCtxRecTyp IS
RECORD (
namespace
VARCHAR2(30),
attribute
VARCHAR2(30),
value
VARCHAR2(256));
TYPE AppCtxTabTyp IS
TABLE OF AppCtxRecTyp INDEX BY
BINARY_INTEGER;
The
list_context procedure is also
rather simple; it returns an array of the
contexts using this data type and the count
of returned entries.
DBMS_SESSION.LIST_CONTEXT (list OUT
AppCtxTabTyp, size OUT NUMBER);
Here is an example of using this procedure:
.list_context_demo.sql script
SET SERVEROUTPUT ON
DECLARE
array_size INT;
array_recs
DBMS_SESSION.AppCtxTabTyp;
BEGIN
array_size := 0;
DBMS_SESSION.LIST_CONTEXT (array_recs,
array_size);
for i in 1 ..
array_size loop
DBMS_OUTPUT.PUT_LINE('Context Name = '
|| array_recs(i).namespace);
DBMS_OUTPUT.PUT_LINE('...Atrribute = '
|| array_recs(i).attribute);
DBMS_OUTPUT.PUT_LINE('...Value
= ' || array_recs(i).value);
end loop;
END;
/
The remaining three procedures are entirely
for managing the corresponding global
application context, which is necessary for
RLS and/or VPD and whose examples are in the
section on role level security.
Set_context
sets the specified context for a given
namespace, of which there are four types:
session local, globally initialized,
externally initialized, and globally
accessed.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAMESPACE
|
VARCHAR2
|
IN
|
|
|
ATTRIBUTE
|
VARCHAR2
|
IN
|
|
|
VALUE
|
VARCHAR2
|
IN
|
|
|
USERNAME
|
VARCHAR2
|
IN
|
NULL
|
|
CLIENT_ID
|
VARCHAR2
|
IN
|
NULL
|
Table 6.133:
Set_context Parameters
Clear_context
clears the named context for a given
namespace.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAME_SPACE
|
VARCHAR2
|
IN
|
|
|
CLIENT_IDENTIFIER
|
VARCHAR2
|
IN
|
|
|
ATTRIBUTE
|
VARCHAR2
|
IN
|
|
Table 6.134:
Clear_context Parameters
Clear_all_context
clears all contexts for a given namespace.
|
Argument
|
Type
|
In / Out
|
Default Value
|
|
NAME_SPACE
|
VARCHAR2
|
IN
|
|
Table 6.135:
Clear_all_context Parameters
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
