Subject:  HOW TO DETERMINE WHAT EVENTS/PARAMETERS ARE SET IN THE CURRENT SESSION 
Creation Date:  24-MAY-1995 

PURPOSE
-------

To explain how to find out the current value of parameters or events set 
using the 'alter session' command.

SCOPE & APPLICATION
-------------------
This article is intended for users and support analysts.  It references an 
undocumented procedure which therefore can change without notice. The 
script has been tested on Oracle 7.2.x, 7.3.x, 8.0.x and 8.1.5.

Introduction
------------

The 'alter session' command is used to change init<sid>.ora parameters and set
events which will take effect just for the current session. Below describes
how to check these session values, after the command has executed.

For example:

  alter session set optimizer_goal=first_rows ;
  alter session set event '10015 trace name context forever';


Changing init<sid>.ora parameters
---------------------------------

For 'alter session' commands that change init<sid>.ora parameters, prior
to Oracle8, the session value cannot be determined. From Oracle8.0.x, you
can use the following sql to find the session value:

SELECT value FROM v$parameter
WHERE name like '<init.ora parameter name>'; 

Listed below are some init.ora parameters and whether they use the new
dynamic interface:

Parameter              Reflected in v$parameter
---------              ------------------------
sql_trace                  No (since this sets an event)
timed_statistics           Yes
optimizer_goal             Yes (parameter optimizer_mode)


Use this syntax for dynamically altering the initialization parameters: 

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value
ALTER SYSTEM SET parameter_name = value DEFERRED

The command will be recorded in the alert.log

For the complete listing, refer to Oracle8i Reference Release 8.1.x Manual, Chapter 1
Specifying Values in the Parameter File ->  Changing Parameter Values
@ <Doc:a67790."ch1.htm".1302>
@ <Docindex:v$parameter>

Setting events
--------------
For alter session commands that set events, you can use the undocumented 
'dbms_system.read_ev' procedure. 

Eg:
 set serveroutput on
 declare
  event_level number;
 begin
  for i in 10000..10999 loop
     sys.dbms_system.read_ev(i,event_level);
     if (event_level > 0) then
        dbms_output.put_line('Event '||to_char(i)||' set at level '||
                             to_char(event_level));
     end if;
  end loop;
 end;
 /

To demonstrate how the above PL/SQL can be used, create the script 
check_param.sql. You should not that setting the init<sid>.ora parameter 
sql_trace sets the event 10046. Other parameters such as timed_statistics,
optimizer_mode do not set events.

Within the session issue:

SQL> @check_param

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=true;
SQL> alter session set events '10015 trace name context forever, level 3';
SQL> @check_param

Event 10015 set at level 12
Event 10046 set at level 1

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> @check_param

Event 10015 set at level 12
Event 10046 set at level 12

PL/SQL procedure successfully completed.

SQL> alter session set sql_trace=false;
SQL> alter session set events '10015 trace name context off';
SQL> @check_param

PL/SQL procedure successfully completed.


RELATED DOCUMENTS
-----------------
@[NOTE:67860.1] PROCEDURE DBMS_SYSTEM.READ_EV Specification
[BUG:620998] ALTER SESSION FOR TIMED_STATISTICS SHOWS IN V$PARAMETER


ADDITIONAL SEARCH WORDS
-----------------------
current true nls