Spying on the other session

You might want to steal a look at the other session especially for the parameters altered by the session and the diagnostics events set by the the session.

As far as I know, there are no official ways(dynamic performace views) to get those values. But we have a well-known panacea – oradebug!

Session #1 set following parameters and events.

-- session #1
alter session set "_optim_peek_user_binds" = false;
alter session set sql_trace = true;

alter session set events '10046 trace name context forever, level 1';
alter session set events '10053 trace name context forever, level 1';

Errorstack on the level 3 dumps the modified parameters and events.

-- session #2
connect /as sysdba
oradebug setospid 9768
oradebug dump errorstack 3

The trace file contains following section.

...
Dump event group for SESSION
10053 trace name CONTEXT level 1, forever
10046 trace name CONTEXT level 1, forever
Dump event group for SYSTEM
DYNAMICALLY MODIFIED PARAMETERS:
  sql_trace                = TRUE
  _optim_peek_user_binds   = FALSE
  _rowsource_execution_statistics= FALSE
...

If all you want is just to find out the modified parameters, following dump command would to that.

oradebug dump modified_parameters 1

Very good, isn’t it?