Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle DBMS_SESSION
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

The dbms_session package provides a number of useful procedures and functions related to managing and/or controlling sessions. Begin by breaking down those offerings along related lines of usefulness starting with the general purpose ones for simply getting or setting session level attributes or characteristics. They are all fairly straightforward and simple, so no examples are given for them.

 

—- Returns a unique id for a session

DBMS_SESSION.UNIQUE_SESSION_ID RETURN VARCHAR2;

 

—- Returns whether current session is still active

DBMS_SESSION.IS_SESSION_ALIVE (uniqueid VARCHAR2) RETURN BOOLEAN;

 

—- Returns whether named role is enabled for session

DBMS_SESSION.IS_ROLE_ENABLED (rolename VARCHAR2) RETURN BOOLEAN;

 

—- Enables and disables named role for session

—- Same as SQL command: SET ROLE

DBMS_SESSION.SET_ROLE (role_cmd VARCHAR2);

 

—- Permits setting session’s various globalization (NLS) settings

—- Same as SQL command: ALTER SESSION SET nls_parameter = value

DBMS_SESSION.SET_NLS (param VARCHAR2, value VARCHAR2);

 

—- Permits setting session trace flag on or off

—- Same as SQL command: ALTER SESSION SET SQL_TRACE = boolean

DBMS_SESSION.SET_SQL_TRACE (sql_trace boolean);

 

—- Permits closing an open database link

—- Same as SQL command: ALTER SESSION CLOSE DATABSE LINK dblink_name

DBMS_SESSION.CLOSE_DATABASE_LINK (dblink VARCHAR2);

 

—- Frees up unused memory after large operations (> 100K)

DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

 

—- Permits session to de-instantiate (i.e. unload from memory)

-- all packages and their memory, cursors, global variables, etc

DBMS_SESSION.RESET_PACKAGE;

 

—- Permits session to change the current resource consumer group

DBMS_SESSION.switch_current_consumer_group (

   new_consumer_group     IN  VARCHAR2,

   old_consumer_group     OUT VARCHAR2,

   initial_group_on_error IN  BOOLEAN);

 

The remaining procedures and functions primarily support row level security (RLS), also referred to as virtual private databases (VPD) and fine grained access control. Examples of using these functions are shown in the section on row level security, so identify their names, purpose, parameters and defaults.

 

Set_identifier, clear_set_identifier and clear_identifier procedures permit setting and clearing the client ID for the session. The client ID of a session is used to map it to some corresponding global application context, which is necessary for RLS and/or VPD.

 

—- Permits setting the session application-specific identtifier

DBMS_SESSION.SET_IDENTIFIER (client_id VARCHAR2);

 

—- Permits clearing the session application-specific identtifier

DBMS_SESSION.CLEAR_IDENTIFIER;

 

Now move onto procedures and functions specifically for managing contexts for a session. These are a little more complicated and the following data type needs to be worked with when listing a session’s active contexts:

 

TYPE AppCtxRecTyp IS RECORD (

   namespace VARCHAR2(30),

   attribute VARCHAR2(30),

   value     VARCHAR2(256));

 

TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;

 

The list_context procedure is also rather simple; it returns an array of the contexts using this data type and the count of returned entries.

 

DBMS_SESSION.LIST_CONTEXT (list OUT AppCtxTabTyp, size OUT NUMBER);

 

Here is an example of using this procedure:

 

.list_context_demo.sql script

 

SET SERVEROUTPUT ON

DECLARE

  array_size INT;

  array_recs DBMS_SESSION.AppCtxTabTyp;

BEGIN

  array_size := 0;

  DBMS_SESSION.LIST_CONTEXT (array_recs, array_size);

  for i in 1 .. array_size loop

    DBMS_OUTPUT.PUT_LINE('Context Name = ' || array_recs(i).namespace);

    DBMS_OUTPUT.PUT_LINE('...Atrribute = ' || array_recs(i).attribute);

    DBMS_OUTPUT.PUT_LINE('...Value     = ' || array_recs(i).value);

  end loop;

END;

/

 

The remaining three procedures are entirely for managing the corresponding global application context, which is necessary for RLS and/or VPD and whose examples are in the section on role level security.

 

Set_context sets the specified context for a given namespace, of which there are four types: session local, globally initialized, externally initialized, and globally accessed.

  

Argument

Type

In / Out

Default Value

NAMESPACE

VARCHAR2

IN

 

ATTRIBUTE

VARCHAR2

IN

 

VALUE

VARCHAR2

IN

 

USERNAME

VARCHAR2

IN

NULL

CLIENT_ID

VARCHAR2

IN

NULL

Table 6.133:  Set_context Parameters

Clear_context  clears the named context for a given namespace.

 

Argument

Type

In / Out

Default Value

NAME_SPACE

VARCHAR2

IN

 

CLIENT_IDENTIFIER

VARCHAR2

IN

 

ATTRIBUTE

VARCHAR2

IN

 

Table 6.134:  Clear_context Parameters

Clear_all_context clears all contexts for a given namespace.

 

Argument

Type

In / Out

Default Value

NAME_SPACE

VARCHAR2

IN

 

Table 6.135:  Clear_all_context Parameters


Fo
r more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.