Release 8.1.5
A68001-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Supplied Packages Reference Release 8.1.5 A68001-01 |
|
This package provides access to SQL ALTER SESSION and SET ROLE statements, and other session information, from PL/SQL. You can use this to set preferences and security levels.
This package runs with the privileges of calling user, rather than the package owner SYS.
This procedure enables and disables roles. It is equivalent to the SET ROLE SQL statement.
DBMS_SESSION.SET_ROLE ( role_cmd VARCHAR2);
| Parameter | Description |
|---|---|
role_cmd |
This text is appended to "set role" and then run as SQL. |
This procedure turns tracing on or off. It is equivalent to the following SQL statement:
ALTER SESSION SET SQL_TRACE ...
DBMS_SESSION.SET_SQL_TRACE ( sql_trace boolean);
| Parameter | Description |
|---|---|
sql_trace |
|
This procedure sets up your national language support (NLS). It is equivalent to the following SQL statement:
ALTER SESSION SET <nls_parameter> = <value>
DBMS_SESSION.SET_NLS ( param VARCHAR2, value VARCHAR2);
This procedure closes an open database link. It is equivalent to the following SQL statement:
ALTER SESSION CLOSE DATABASE LINK <name>
DBMS_SESSION.CLOSE_DATABASE_LINK ( dblink VARCHAR2);
| Parameter | Description |
|---|---|
dblink |
Name of the database link to close. |
This procedure deinstantiates all packages in this session: It frees all package state.
Memory used for caching execution state is associated with all PL/SQL functions, procedures, and packages that have been run in a session.
For packages, this collection of memory holds the current values of package variables and controls the cache of cursors opened by the respective PL/SQL programs. A call to RESET_PACKAGE frees the memory associated with each of the previously run PL/SQL programs from the session, and, consequently, clears the current values of any package globals and closes any cached cursors.
RESET_PACKAGE can also be used to reliably restart a failed program in a session. If a program containing package variables fails, then it is hard to determine which variables need to be reinitialized. RESET_PACKAGE guarantees that all package variables are reset to their initial values.
DBMS_SESSION.RESET_PACKAGE;
None.
Because the amount of memory consumed by all executed PL/SQL can become large, you might use RESET_PACKAGE to trim down the session memory footprint at certain points in your database application. However, make sure that resetting package variable values will not affect the application. Also, remember that later execution of programs that have lost their cached memory and cursors will perform slower, because they need to recreate the freed memory and cursors.
RESET_PACKAGE does not free the memory, cursors, and package variables immediately when called.
For example, PL/SQL procedure P1 calls PL/SQL procedure P2, and P2 calls RESET_PACKAGE. The RESET_PACKAGE effects do not occur until procedure P1 finishes execution (the PL/SQL call ends).
This SQL*Plus script runs a large program with many PL/SQL program units that may or may not use global variables, but it doesn't need them beyond this execution:
EXCECUTE large_plsql_program1;
To free up PL/SQL cached session memory:
EXECUTE DBMS_SESSION.RESET_PACKAGE;
To run another large program:
EXECUTE large_plsql_program2;
This function returns an identifier that is unique for all sessions currently connected to this database. Multiple calls to this function during the same session always return the same result.
DBMS_SESSION.UNIQUE_SESSION_ID RETURN VARCHAR2;
None.
pragma restrict_references(unique_session_id,WNDS,RNDS,WNPS);
| Return | Description |
|---|---|
unique_session_id |
Returns up to 24 bytes. |
This function determines if the named role is enabled for this session.
DBMS_SESSION.IS_ROLE_ENABLED ( rolename VARCHAR2) RETURN BOOLEAN;
| Parameter | Description |
|---|---|
rolename |
Name of the role. |
| Return | Description |
|---|---|
is_role_enabled |
|
This function determines if the specified session is alive.
DBMS_SESSION.IS_SESSION_ALIVE ( uniqueid VARCHAR2) RETURN BOOLEAN;
| Parameter | Description |
|---|---|
uniqueid |
Unique ID of the session: This is the same one as returned by |
| Return | Description |
|---|---|
is_session_alive |
|
This procedure turns close_cached_open_cursors on or off. It is equivalent to the following SQL statement:
ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS ...
DBMS_SESSION.SET_CLOSE_CACHED_OPEN_CURSORS ( close_cursors BOOLEAN);
| Parameter | Description |
|---|---|
close_cursors |
|
This procedure reclaims unused memory after performing operations requiring large amounts of memory (more than 100K).
Examples of operations that use large amounts of memory include:
sort_area_size is used and sort_area_size is hundreds of KB.
You can monitor user memory by tracking the statistics "session uga memory" and "session pga memory" in the v$sesstat or v$statname fixed views. Monitoring these statistics also shows how much memory this procedure has freed.
DBMS_SESSION.FREE_UNUSED_USER_MEMORY;
None.
The behavior of this procedure depends upon the configuration of the server operating on behalf of the client:
shared_pool. Session memory is allocated from the shared_pool in this configuration.
In order to free memory using this procedure, the memory must not be in use.
After an operation allocates memory, only the same type of operation can reuse the allocated memory. For example, after memory is allocated for sort, even if the sort is complete and the memory is no longer in use, only another sort can reuse the sort-allocated memory. For both sort and compilation, after the operation is complete, the memory is no longer in use, and the user can call this procedure to free the unused memory.
An indexed table implicitly allocates memory to store values assigned to the indexed table's elements. Thus, the more elements in an indexed table, the more memory the RDBMS allocates to the indexed table. As long as there are elements within the indexed table, the memory associated with an indexed table is in use.
The scope of indexed tables determines how long their memory is in use. Indexed tables declared globally are indexed tables declared in packages or package bodies. They allocate memory from session memory. For an indexed table declared globally, the memory remains in use for the lifetime of a user's login (lifetime of a user's session), and is freed after the user disconnects from ORACLE.
Indexed tables declared locally are indexed tables declared within functions, procedures, or anonymous blocks. These indexed tables allocate memory from PGA memory. For an indexed table declared locally, the memory remains in use for as long as the user is still running the procedure, function, or anonymous block in which the indexed table is declared.After the procedure, function, or anonymous block is finished running, the memory is then available for other locally declared indexed tables to use (in other words, the memory is no longer in use).
Assigning an uninitialized, "empty" indexed table to an existing index table is a method to explicitly re-initialize the indexed table and the memory associated with the indexed table. After this operation, the memory associated with the indexed table is no longer in use, making it available to be freed by calling this procedure. This method is particularly useful on indexed tables declared globally which can grow during the lifetime of a user's session, as long as the user no longer needs the contents of the indexed table.
The memory rules associated with an indexed table's scope still apply; this method and this procedure, however, allow users to intervene and to explicitly free the memory associated with an indexed table.
The PL/SQL fragment below illustrates the method and the use of procedure FREE_UNUSED_USER_MEMORY.
CREATE PACKAGE foobar type number_idx_tbl is table of number indexed 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; -- end of foobar DECLARE ... empty_table number_idx_tbl; -- uninitialized ("empty") version BEGIN FOR i in 1..1000000 loop store1_table(i) := i; -- load data END LOOP; ... store1_table := empty_table; -- "truncate" the indexed table ... - dbms_session.free_unused_user_memory; -- give memory back to system store1_table(1) := 100; -- index tables still declared; store2_table(2) := 200; -- but truncated. ... END;
This procedure sets or resets the value of a context attribute.
DBMS_SESSION.SET_CONTEXT ( namespace VARCHAR2, attribute VARCHAR2, value VARCHAR2);
The caller of this function must be in the calling stack of a procedure which has been associated to the context namespace through a CREATE CONTEXT statement. The checking of the calling stack does not cross DBMS boundary.
There is no limit on the number of attributes that can be set in a namespace. An attribute value remains for user session, or until it is reset by the user.
This procedure returns a list of active namespaces and contexts for the current session.
TYPE AppCtxRecTyp IS RECORD ( namespace VARCHAR2(30), attribute VARCHAR2(30), value VARCHAR2(256)); TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER; DBMS_SESSION.LIST_CONTEXT ( list OUT AppCtxTabTyp, size OUT NUMBER);
| Parameter | Description |
|---|---|
list |
Buffer to store a list of application context set in the current session. |
| Return | Description |
|---|---|
list |
A list of (namespace, attribute, values) set in current session |
size |
Returns the number of entries in the buffer returned |
The context information in the list appears as a series of <namespace> <attribute> <value>. Because list is a table type variable, its size is dynamically adjusted to the size of returned list.
This procedure changes the current resource consumer group of a user's current session.
This lets you switch to a consumer group if you have the switch privilege for that particular group. If the caller is another procedure, then this enables the user to switch to a consumer group for which the owner of that procedure has switch privilege.
DBMS_SESSION.switch_current_consumer_group ( new_consumer_group IN VARCHAR2, old_consumer_group OUT VARCHAR2, initial_group_on_error IN BOOLEAN);
This procedure outputs the old consumer group of the user in the parameter old_consumer_group.
| Exception | Description |
|---|---|
29368 |
Non-existent consumer group. |
1031 |
Insufficient privileges. |
29396 |
Cannot switch to |
The owner of a procedure must have privileges on the group from which a user was switched (old_consumer_group) in order to switch them back. There is one exception: The procedure can always switch the user back to his/her initial consumer group (skipping the privilege check).
By setting initial_group_on_error to TRUE, SWITCH_CURRENT_CONSUMER_GROUP puts the current session into the default group, if it can't put it into the group designated by new_consumer_group. The error associated with the attempt to move a session into new_consumer_group is raised, even though the current consumer group has been changed to the initial consumer group.
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 -- perform some operations while under privileged group 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 cannot do so, switch -- to caller's initial consumer group dbms_session.switch_current_consumer_group(old_group,prev_group,TRUE); END high_priority_task; /
