CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_SESSION
Version 11.1
 
General
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.
Source {ORACLE_HOME}/rdbms/admin/dbmssess.sql
First Available 7.3.4
Constants used by Modify Package State
Name Data Type Value
FREE_ALL_RESOURCES PLS_INTEGER 1
REINITIALIZE PLS_INTEGER 2

Defined 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;
/

Dependencies
CURRENTSESSIONTOKENMAP$ ODM_MODEL_UTIL
DBMS_ALERT PBSDE
DBMS_DEBUG ROWTOKENMAP$
DBMS_REGISTRY_SYS SDO_SEM_CTX
DBMS_SNAPSHOT SDO_WFS_LOCK
DBMS_SQLDIAG_INTERNAL SESSION_CONTEXT
DBMS_STATS_INTERNAL STORAGE_UI_UTIL_PKG
DBMS_SUMREF_UTIL TOKENSESSIONMAP$
DRIACC WM_DDL_UTIL
DRIXMD WWV_FLOW
KUPD$DATA WWV_FLOW_DATALOAD_XML
KUPW$WORKER WWV_FLOW_LANG
LTADM WWV_FLOW_SC_TRANSACTIONS
LT_CTX_PKG WWV_FLOW_UTILITIES
MGMT_USER  
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
Security Model Execute is granted to PUBLIC
 
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
 
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 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;
/
 
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
 
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
 
MODIFY_PACKAGE_STATE
Performs actions on the session state of PL/SQL program units that are active in the session
Compiles on the client dbms_session.modify_package_state(action_flags IN PLS_INTEGER);

-- See action_flag constants above
dbms_session.modify_package_state(2);
Free memory resources exec dbms_session.modify_package_state(dbms_session.free_all_resources);
 
RESET_PACKAGE
Deinstantiates all packages in this 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 (new 11g plan_stat parameter)

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_CLOSED_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 / as sysdba

GRANT create any context TO uwclass;

conn uwclass/uwclass

CREATE OR REPLACE CONTEXT sc_test USING set_contxt;

CREATE OR REPLACE PROCEDURE set_contxt 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 (new 11g)
Requests a switch to the specified edition.   The switch takes effect at the end of the current client call

Not implemented in 11gR1
dbms_session.set_edition(edition IN VARCHAR2);
SELECT *
FROM dba_editions;

exec dbms_session.set_edition('ORA$BASE');
 
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;

-- 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-YY''');
 
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 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 while 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
 
Related Topics
Database Links
Packages
Roles
SYS_CONTEXT
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [67 users online]    © 2010 psoug.org