Oracle DBMS_SESSION
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Try dbms_session.reset_package. This call will reset all packages variables to their defaults (and will close and flush all cached cursors and free other resources, too, but it will not reset system contexts and it will not cause package initialization part to be re-executed when package is next accessed). Note that no explicit default for a package variable defaults it to NULL.
AUTHID CURRENT_USER
Constants
Name Data Type Value
FREE_ALL_RESOURCES PLS_INTEGER 1
REINITIALIZE PLS_INTEGER 2
Data Types TYPE AppCtxRecTyp IS RECORD (
namespace VARCHAR2(30),
attribute VARCHAR2(30),
value     VARCHAR2(4000));
/

TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;
/

TYPE lname_array IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
/

TYPE integer_array IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
/
Dependencies
CURRENTSESSIONTOKENMAP$ DBMS_SUMREF_UTIL SESSION_CONTEXT
DBMS_ALERT DBMS_UTILITY TOKENSESSIONMAP$
DBMS_AQJMS DRIACC WWV_FLOW_CREATE_APP_FROM_QUERY
DBMS_DBFS_CONTENT_ADMIN DRIXMD WWV_FLOW_DATALOAD_XML
DBMS_DEBUG KUPD$DATA WWV_FLOW_DATA_UPLOAD
DBMS_DM_UTIL KUPW$WORKER WWV_FLOW_DYNAMIC_EXEC
DBMS_LOGSTDBY_CONTEXT LBAC_CACHE WWV_FLOW_FLASH_CHART5
DBMS_MACOLS LTADM WWV_FLOW_LANG
DBMS_MACSEC_ROLES LTUTIL WWV_FLOW_LOAD_DATA
DBMS_REGISTRY_SYS LT_CTX_PKG WWV_FLOW_SC_TRANSACTIONS
DBMS_SNAPSHOT PBSDE WWV_FLOW_SECURITY
DBMS_SQLTCB_INTERNAL PLITBLM WWV_FLOW_UTILITIES
DBMS_STANDARD ROWTOKENMAP$ WWV_FLOW_WORKSHEET_STANDARD
DBMS_STATS_INTERNAL SDO_WFS_LOCK XS_DIAG_INT
Documented Yes
Exceptions
Error Code Reason
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
First Available 7.3.4
Security Model Owned by SYS with EXECUTE granted to PUBLIC, DVSYS, and the APEX_040200 and DBFS_ROLE roles.
Source {ORACLE_HOME}/rdbms/admin/dbmssess.sql
Subprograms
 
CLEAR_ALL_CONTEXT
Removes all contexts dbms_session.clear_all_context(namespace IN VARCHAR2);
exec dbms_session.clear_all_context('SC_TEST');
 
CLEAR_CONTEXT
Removes a context dbms_session.clear_context(
namespace         IN VARCHAR2,
client_identifier IN VARCHAR2 DEFAULT NULL,
attribute         IN VARCHAR2 DEFAULT NULL);
see SET_CONTEXT Demo Below
 
CLEAR_IDENTIFIER
Removes the set_client_id in the session dbms_session.clear_identifier;
exec dbms_session.clear_identifier;
 
CLOSE_DATABASE_LINK
Closes an open database link dbms_session.close_database_link(dblink IN VARCHAR2);

equivalent to: ALTER SESSION CLOSE DATABASE LINK <name>;
exec dbms_session.close_database_link('TEST_LINK');
 
FREE_UNUSED_USER_MEMORY
Reclaims unused memory after performing operations requiring large amounts of memory (more than 100K) dbms_session.free_unused_user_memory;
CREATE OR REPLACE PACKAGE foobar AUTHID DEFINER AS
 TYPE number_idx_tbl IS TABLE OF NUMBER
 INDEX BY binary_integer;

 store1_table number_idx_tbl; -- PL/SQL indexed table
 store2_table number_idx_tbl; -- PL/SQL indexed table
 store3_table number_idx_tbl; -- PL/SQL indexed table
END foobar;
/

DECLARE
 empty_table foobar.number_idx_tbl;   -- uninitialized
BEGIN
  FOR i in 1..1000000
  LOOP
    foobar.store1_table(i) := i;      -- load data
  END LOOP;

  foobar.store1_table := empty_table; -- empty indexed table

  dbms_session.free_unused_user_memory;

  foobar.store1_table(1) := 100;      -- index tables declared
  foobar.store1_table(2) := 200;      -- but truncated
END;
/
 
GET_PACKAGE_MEMORY_UTILIZATION
This procedure describes static package memory usage. The output collections describe memory usage in each instantiated package.

Overload 1
dbms_session.get_package_memory_utilization(
owner_names  OUT NOCOPY lname_array,
unit_names   OUT NOCOPY lname_array,
unit_types   OUT NOCOPY integer_array,
used_amounts OUT NOCOPY integer_array,
free_amounts OUT NOCOPY integer_array);
set serveroutput on

DECLARE
 o_names dbms_session.lname_array;
 u_names dbms_session.lname_array;
 u_types dbms_session.integer_array;
 u_amnts dbms_session.integer_array;
 f_amnts dbms_session.integer_array;
BEGIN
  dbms_output.put_line('Owner                          Unit                                 Type       Used       Free');
  dbms_session.get_package_memory_utilization(o_names, u_names, u_types, u_amnts, f_amnts);
  FOR i IN 1 .. o_names.COUNT LOOP
    dbms_output.put_line(RPAD(o_names(i),30,' ') || ' ' || RPAD(u_names(i),30,' ') || ' ' || LPAD(u_types(i),10,' ') || ' ' || LPAD(u_amnts(i),10,' ') || ' ' || LPAD(f_amnts(i),10,' '));
  END LOOP;
END;
/
Overload 2 dbms_session.get_package_memory_utilization(
 
 
IS_ROLE_ENABLED
Determines if the named role is enabled for this session dbms_session.is_role_enabled(rolename IN VARCHAR2) RETURN BOOLEAN;
See SET_ROLE Demo Below
 
IS_SESSION_ALIVE
Determines if the specified session is active dbms_session.is_session_alive(uniqueid IN VARCHAR2) RETURN BOOLEAN;
-- as uwclass
SELECT dbms_session.unique_session_id
FROM dual;

-- as SYS
set serveroutput on

DECLARE
 x BOOLEAN;
BEGIN
  IF dbms_session.is_session_alive('008D04030001') THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/
 
LIST_CONTEXT
Returns a list of active namespaces and contexts for the current session dbms_session.list_context(list OUT AppCtxTabTyp, lsize OUT NUMBER);
See SET_CONTEXT Demo Below
 
MODIFY_PACKAGE_STATE
Frees memory resources. Performs actions on the session state of PL/SQL program units that are active in the session. dbms_session.modify_package_state(action_flags IN PLS_INTEGER);

-- See ACTION_FLAG constants above
exec dbms_session.modify_package_state(2);

exec dbms_session.modify_package_state(dbms_session.free_all_resources);
 
RESET_PACKAGE
Deinstantiates all packages in the current session dbms_session.reset_package;
exec dbms_session.reset_package;
 
SESSION_TRACE_DISABLE
Disable SQL trace for the session dbms_session.session_trace_disable;
exec dbms_session.session_trace_disable;
 
SESSION_TRACE_ENABLE
Enables SQL trace for the session dbms_session.session_trace_enable(
waits     IN BOOLEAN  DEFAULT TRUE,
binds     IN BOOLEAN  DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);

PLAN_STAT Description
ALL_EXECUTIONS Always dump row source statistics
FIRST_EXECUTION Dump row source statistics on first execution
NEVER Never dump row source statistics
exec dbms_session.session_trace_enable;
 
SET_CLOSE_CACHED_OPEN_CURSORS
Turns close_cached_open_cursors on or off dbms_session.set_close_cached_open_cursors(close_cursors IN BOOLEAN);

-- equivalent to: ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS
exec dbms_session.set_close_cached_open_cursors(TRUE);
 
SET_CONTEXT
Sets a context within a session dbms_session.set_context(
namespace IN VARCHAR2,
attribute IN VARCHAR2,
value     IN VARCHAR2,
username  IN VARCHAR2 DEFAULT NULL,
client_id IN VARCHAR2 DEFAULT NULL);
conn sys@pdbdev as sysdba

GRANT create any context TO uwclass;

conn uwclass/uwclass@pdbdev

CREATE OR REPLACE CONTEXT sc_test USING set_contxt;

CREATE OR REPLACE PROCEDURE set_contxt AUTHID DEFINER IS
 lcontext dbms_session.appctxtabtyp;
 lsize    NUMBER;
BEGIN
  FOR i IN 1..5 LOOP
    dbms_session.set_context('SC_TEST', 'ATTR' || i, i * 100);
  END LOOP;

  FOR j IN (SELECT * FROM session_context ORDER BY 2) LOOP
    dbms_output.put_line(j.attribute || ', ' || j.value);
  END LOOP;

  dbms_output.put_line('*****');
  dbms_session.list_context (lcontext, lsize);
  FOR k IN 1 .. (lcontext.COUNT)
  LOOP
    dbms_output.put_line('Namespace: ' || lcontext(k).namespace || ' Attribute: ' ||
                          lcontext(k).attribute || ' Value: ' || lcontext(k).value);
  END LOOP;
  dbms_output.put_line('*****');

  dbms_output.put_line('List Size: ' || lsize);

  dbms_session.clear_context('SC_TEST', NULL, 'ATTR2');
  dbms_output.put_line( '*** ATTR2 has now been cleared ***' );

  FOR l IN (SELECT * FROM session_context ORDER BY 2) LOOP
    dbms_output.put_line(l.attribute || ', ' || l.value);
  END LOOP;
END set_contxt;
/

set serveroutput on

exec set_contxt
 
SET_EDITION_DEFERRED
Requests a switch to the specified edition at the end of the current client call dbms_session.set_edition_deferred(edition IN VARCHAR2);
SELECT *
FROM dba_editions;

CREATE EDITION ORA$TEST;

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

exec dbms_session.set_edition_deferred('ORA$TEST');

SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

exec dbms_session.set_edition_deferred('ORA$BASE');

ALTER EDITION ORA$TEST UNUSABLE;

DROP EDITION ORA$TEST;
 
SET_IDENTIFIER
Sets the client ID in the session dbms_session.set_identifier(client_id IN VARCHAR2);
/* internal services SYS$BACKGROUND is used by background processes only SYS$USERS is the default for user sessions not associated with other services */

set linesize 160
col client_identifier format a25
col client_id format a20
col event format a25
col service_name format a25
col module format a25

SELECT sid, client_identifier, service_name, module
FROM gv_$session;

CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON
ON DATABASE
DECLARE
 uid  VARCHAR2(64);
BEGIN
  SELECT ora_login_user ||':'||SYS_CONTEXT('USERENV','OS_USER')
  INTO uid
  FROM dual;

  dbms_session.set_identifier(uid);
END logon_trigger;
/

SELECT sid, client_identifier, service_name, module, action
FROM gv$session
WHERE service_name <> 'SYS$BACKGROUND';

-- session 2
log on

exec dbms_application_info.set_module('Battelle', 'Lecture');

-- session 1
SELECT sid, client_identifier, service_name, module, action
FROM gv_$session;

-- ASH
SELECT session_id, client_id, event,
SUM(wait_time + time_waited) ttl_wait_time
FROM gv_$active_session_history
WHERE sample_time BETWEEN SYSDATE - 30/1440 AND SYSDATE
GROUP BY session_id, client_id, event
ORDER BY 2;
 
SET_NLS
Sets national language support (NLS) dbms_session.set_nls(param IN VARCHAR2, value IN VARCHAR2);

-- equivalent to: ALTER SESSION SET <nls_parameter> = <value>;
exec dbms_session.set_nls('nls_date_format','''DD-MON-YYYY''');
 
SET_ROLE
Enables and disables roles dbms_session.set_role(role_cmd IN VARCHAR2);
set serveroutput on

BEGIN
  IF dbms_session.is_role_enabled('CONNECT') THEN
    dbms_output.put_line('CONNECT is granted');
  ELSE
    dbms_output.put_line('CONNECT is not granted');
  END IF;
END;
/

exec dbms_session.set_role('NONE');

BEGIN
  IF dbms_session.is_role_enabled('CONNECT') THEN
    dbms_output.put_line('CONNECT is granted');
  ELSE
    dbms_output.put_line('CONNECT is not granted');
  END IF;
END;
/

CREATE TABLE set_role_tab (
testcol VARCHAR2(20));

desc user_role_privs

SELECT granted_role
FROM user_role_privs;

exec dbms_session.set_role('CONNECT');

BEGIN
  IF dbms_session.is_role_enabled('CONNECT') THEN
    dbms_output.put_line('CONNECT is granted');
  ELSE
    dbms_output.put_line('CONNECT is not granted');
  END IF;
END;
/

CREATE TABLE set_role_tab (
testcol VARCHAR2(20));
 
SET_SQL_TRACE
Turns tracing on or off dbms_session.set_sql_trace(sql_trace IN BOOLEAN);

-- equivalent to: ALTER SESSION SET SQL_TRACE <ON | OFF>;
exec dbms_session.set_sql_trace(TRUE);
 
SWITCH_CURRENT_CONSUMER_GROUP
Changes the current resource consumer group of a user's current session dbms_session.switch_current_consumer_group(
new_consumer_group     IN  VARCHAR2,
old_consumer_group     OUT VARCHAR2,
initial_group_on_error IN  BOOLEAN);
CREATE OR REPLACE PROCEDURE high_priority_task AUTHID DEFINER IS
 old_group  VARCHAR2(30);
 prev_group VARCHAR2(30);
 curr_user  VARCHAR2(30);
BEGIN
  /* switch invoker to privileged consumer group. If we fail to do so, an  error will be thrown, but the consumer group will not change because 'initial_group_on_error' is set to FALSE */
  dbms_session.switch_current_consumer_group('tkrogrp1',
  old_group, FALSE);

  --set up exception handler (in the event of an error, we do not want to return to caller leaving the session still in the privileged group)
  BEGIN
    NULL;  -- perform some actions
  EXCEPTION
    WHEN OTHERS THEN
    /* It is possible that the procedure owner does not have privileges on old_group. 'initial_group_on_error' is set to TRUE to make sure that the user is moved out of the privileged group in such a situation */
  dbms_session.switch_current_consumer_group
(old_group, prev_group, TRUE);
    RAISE;
  END;
  --we've succeeded. Now switch to old_group, or if can not do so, switch to caller's initial consumer group
  dbms_session.switch_current_consumer_group(old_group, prev_group, TRUE);
END high_priority_task;
/
 
UNIQUE_SESSION_ID
Returns an identifier that is unique for all sessions currently connected to this database dbms_session.unique_session_id RETURN VARCHAR2;
See IS_SESSION_ALIVE Demo Above
 
USE_DEFAULT_EDITION_DEFERRED (new 12.1)
Disassociates the session from its current edition  at the end of the current call dbms_session.use_default_edition_deferred;
exec dbms_session.use_default_edition_deferred;

Related Topics
Database Links
Edition Based Redefinition
Packages
Roles
SYS_CONTEXT

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved