How to get dump or list parameters set at session level?

Using oradebug one can get a dump of sessions parameters that are modified at session level, like optimization parameters.

SQL> alter session set sql_trace=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

-- connect to session
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump modified_parameters 1;
Statement processed.
SQL> oradebug tracefile_name;
i:\db\oracle\testdb\diagnostic_dest\diag\rdbms\testdb_a\testdb\trace\testdb_ora_4908.trc

Contents of the tracefile:

*** 2012-05-28 14:35:25.005
Processing Oradebug command ‘dump modified_parameters 1’
DYNAMICALLY MODIFIED PARAMETERS:
sql_trace = TRUE
optimizer_mode = FIRST_ROWS

*** 2012-05-28 14:35:25.005
Oradebug command ‘dump modified_parameters 1’ console output:

Advertisements

Using oradebug to dump call stack?

Using “oradebug dump errorstack <dump level>” one can dump the call stack of the process after attaching to a running session. There are 3 values that can be passed as dump level
0 – dump error buffer
1 – level 0 with call stack
2 – level 1 with process state
3 – level 2 with context area

Example:
— Attach to a process
SQL> oradebug setospid 21906;
Oracle pid: 17, Unix process pid: 21906, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug dump errorstack 3;
Statement processed.
SQL> oradebug tracefile_name;
/u01/oradata/admin/TEST/udump/test_ora_21906.trc

Information in the trace file:

*** 2010-07-22 21:35:56.554
*** SERVICE NAME:(SYS$USERS) 2010-07-22 21:35:56.554
*** SESSION ID:(27.1875) 2010-07-22 21:35:56.554
Received ORADEBUG command ‘dump errorstack 3’ from process Unix process pid: 22990, image:
*** 2010-07-22 21:35:56.554
ksedmp: internal or fatal error
—– Call Stack Trace —–
calling call entry argument values in hex
location type point (? means dubious value)
——————– ——– ——————– —————————-
Cannot find symbol
Cannot find symbol
Cannot find symbol
ksedst()+31 call ksedst1() 000000001 ? 000000001 ?
000000000 ? 000000000 ?
000000000 ? 000000001 ?
ksedmp()+610 call ksedst() 000000001 ? 000000001 ?

..

How to find/list the events set in a session?

Using “oradebug dump events 1” one can find/list events set in a session.

For example:
In a session events 10046 and 1410 events are set
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';

Session altered.

SQL> ALTER SESSION SET EVENTS='1410 trace name context forever, level 12';

Session altered.

SQL> SELECT distinct sid FROM v$mystat
SID
———-
131
In another session login as an account as sysdba
SQL> SELECT pid, spid FROM v$process where addr IN (SELECT paddr FROM V$session where sid = 131);
PID SPID
———- ——-
12 3420

SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)

SQL> oradebug dump events 1
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_10932.trc

Or in 11g:
SQL> oradebug eventdump session
1410 trace name context forever, level 12
sql_trace level=12

After running the above oradebug will dump the events in the trace file in the above case /u01/oradata/admin/TEST/udump/test_ora_10932.trc shows event 1410 and 10046 events set.

Dump event group for level SESSION
TC Addr Evt#(b10) Action TR Addr Arm Life
974FED50 1410 1 974fede0 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
974FEBF8 10046 1 974fec88 0 0
TR Name TR level TR address TR arm TR life TR type
CONTEXT 12 0 -1 2 0
*** 2010-07-15 23:40:05.671
..

How to suspend/resume a process using oradebug?

Suspend a running process
SQL> oradebug setorapid 12
Unix process pid: 10932, image: oracle@localhost.localdomain (TNS V1-V3)
SQL> oradebug suspend
Statement processed.

Resume a process
SQL> oradebug resume
Statement processed.

If you have trace turned on a suspended and resume session you will see the following messages in the trace file.
FETCH #5:c=54992,e=55962,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098751175228
EXEC #5:c=0,e=72,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098751175408
Received ORADEBUG command ‘suspend’ from process Unix process pid: 22990, image:
*** 2010-07-13 21:12:34.268
Received ORADEBUG command ‘resume’ from process Unix process pid: 22990, image:
FETCH #5:c=58991,e=32316703,p=0,cr=2334,cu=0,mis=0,r=1,dep=1,og=1,tim=1249098783492125
EXEC #5:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1249098783492321

Using oradebug to set event

The following example shows how to set event on a oracle session

To turn on event, needs to be run as sysdba
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.

To turn off event
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.

List of events can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

How to find the trace file name?

To find the trace file one using oradebug one can find it using the example below:

SQL> oradebug mysetpid
Statement processed.

SQL> oradebug tracefile_name
/u01/oradata/admin/TEST/udump/test_ora_9964.trc

If you try to run the above command without attaching to a session it will return ORA-00074
SQL> oradebug tracefile_name
ORA-00074: no process has been specified