Oracle | DBMS

Oracle DBMS_SESSION Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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
General
FREE_ALL_RESOURCES	PLS_INTEGER	1
REINITIALIZE	PLS_INTEGER	2
Memory Utilization
USED_MEMORY	BINARY_INTEGER	1
FREE_MEMORY	BINARY_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 big_integer_array IS TABLE OF INTEGER index by BINARY_INTEGER; TYPE big_integer_matrix IS TABLE OF big_integer_array 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_SNAPSHOT_KKXRCA	PBSDE
DBMS_ALERT	DBMS_SQLTCB_INTERNAL	PLITBLM
DBMS_AQJMS	DBMS_STANDARD	PRVTEMX_SQL
DBMS_ASSERT	DBMS_STATS_INTERNAL	ROWTOKENMAP$
DBMS_DBFS_CONTENT_ADMIN	DBMS_SYS_ERROR	SDO_RDF_INTERNAL
DBMS_DEBUG	DBMS_UTILITY	SDO_SEM_CTX
DBMS_GSM_POOLADMIN	DRIXMD	SDO_SEM_CTX_SESSION
DBMS_GSM_UTILITY	KUPD$DATA	SDO_SEM_HTTP_CTX
DBMS_HADOOP	KUPW$WORKER	SDO_SEM_UPDATE_CTX
DBMS_LOCK	LBAC_CACHE	SDO_WFS_LOCK
DBMS_LOGSTDBY_CONTEXT	LTADM	SESSION_CONTEXT
DBMS_MACOLS	LTUTIL	TKHCS_LOG_PKG
DBMS_MACSEC_ROLES	LT_CTX_PKG	TOKENSESSIONMAP$
DBMS_REGISTRY_SYS	OPG_CTX	XS_DIAG_INT
DBMS_SESSION	OPG_CTX_SESSION

Documented Yes

Exceptions

Error Code	Reason
ORA-01919	Role <role_name> does not exist
ORA-01979	Missing or invalid password for role <role_name>
ORA-01924	Role <role_name> not granted or does not exist

First Available 7.3.4

Security Model Owned by SYS with EXECUTE granted to AUDSYS, DVSYS, PUBLIC, SYSRAC

Source {ORACLE_HOME}/rdbms/admin/dbmssess.sql

Subprograms

CLEAR_ALL_CONTEXT	MODIFY_PACKAGE_STATE	SET_IDENTIFIER
CLEAR_CONTEXT	RESET_PACKAGE	SET_NLS
CLEAR_IDENTIFIER	SESSION_IS_ROLE_ENABLED	SET_ROLE
CLOSE_DATABASE_LINK	SESSION_TRACE_DISABLE	SET_SQL_TRACE
CURRENT_IS_ROLE_ENABLED	SESSION_TRACE_ENABLE	SLEEP
FREE_UNUSED_USER_MEMORY	SET_CLOSE_CACHED_OPEN_CURSORS	SWITCH_CURRENT_CONSUMER_GROUP
GET_PACKAGE_MEMORY_UTILIZATION	SET_CONTEXT	UNIQUE_SESSION_ID
IS_ROLE_ENABLED	SET_CURRENT_SCHEMA_DEFERRED	USE_DEFAULT_EDITION_ALWAYS
IS_SESSION_ALIVE	SET_EDITION_DEFERRED	USE_DEFAULT_EDITION_DEFERRED
LIST_CONTEXT

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');

CURRENT_IS_ROLE_ENABLED

Returns TRUE if the named role is currently enabled dbms_session.current_is_role_enabled(rolename IN VARCHAR2) RETURN BOOLEAN;

See SET_ROLE demo below

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( desired_info IN integer_array, owner_names OUT NOCOPY lname_array, unit_names OUT NOCOPY lname_array, unit_types OUT NOCOPY integer_array, amounts OUT NOCOPY big_integer_matrix);

TBD

IS_ROLE_ENABLED

Determines if the named role is enabled for this session dbms_session.is_role_enabled(rolename IN VARCHAR2) RETURN BOOLEAN;

Deprecated as of 19.2 see replacement functions in this package

IS_SESSION_ALIVE

Determines if the specified session is active dbms_session.is_session_alive(uniqueid IN VARCHAR2) RETURN BOOLEAN;

conn uwclass/uwclass @pdbdev

      

      SELECT dbms_session.unique_session_id

      FROM dual;

      

      conn sys@pdbdev as sysdba

      

      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

Alters the session state of PL/SQL program units that are active in the session freeing memory resources. 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_IS_ROLE_ENABLED

Returns TRUE if the named role is enabled at the session level dbms_session.session_is_role_enabled(rolename IN VARCHAR2) RETURN BOOLEAN;

See SET_ROLE demo below

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_CURRENT_SCHEMA_DEFERRED

Set the current schema of the session to a schema specified in the call. The switch takes effect at the end of the current client call. dbms_session.set_current_schema_deferred(schema_name IN VARCHAR2);

exec dbms_session.set_current_schema_deferred('SCOTT');

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.current_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.session_is_role_enabled('CONNECT') THEN

          dbms_output.put_line('CONNECT is enabled in this session');

        ELSE

          dbms_output.put_line('CONNECT is not enabled in this session');

        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.current_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);

SLEEP

Suspends the session for a given period of time dbms_session.sleep(seconds IN NUMBER);

exec dbms_session.sleep(10);

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_ALWAYS

Toggles the mode that disassociates the session from an edition at the end of every client call. Following each call, and before the next top level call, no edition will be in use by the session. The session will use the reigning database default edition for its next operation. dbms_session.use_default_edition_always(mode_on IN BOOLEAN DEFAULT TRUE);

exec dbms_session.use_default_edition_always(FALSE);

USE_DEFAULT_EDITION_DEFERRED

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;