| 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 | | Role "rolename" does not exist | | Missing or invalid password for role "rolename" | | 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 |
| |
|
| 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 | execdbms_session.modify_package_state(dbms_session.free_all_resources); |
| |
|
| 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 |