Miladin Modrakovic's Blog: Oraclue

Got this idea surfing Tanel’s post on Oracle’s dignostic events .He explained some unkown syntax for setting oracle diagnostic events.Full article can be found at:

http://blog.tanelpoder.com/2009/03/03/the-full-power-of-oracles-diagnostic-events-part-1-syntax-for-ksd-debug-event-handling/

It’s a very good post.In addition to this syntax there are  two  more options  for action keyword that I have used in the past.

Let me start with syntax that’s everyone familiar with:

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

action keyword or  trace in this case is most used.

Next one in line would be word debugger/debug

Debugger -  invokes system debugger

examples:

SQL> alter session set events ‘immediate debugger ‘;

Session altered.

 

OR   SQL> alter session set events ‘immediate debug’;

Session altered.

SQL> alter session set events ‘parse_sql_statement debugger’;

Session altered.

SQL> alter session set events ’10117 debugger’;

Session altered.

 

You can use debuger to call  script or command:

vi debug.sh

/bin/echo Hello World! $*

SQL> alter system set”_oradbg_pathname”=’/oracle/admin/test/scripts/debug.sh’;

System altered.

SQL> alter system set events ‘logon debugger’;

System altered.

SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
-sh-3.1$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Mar

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Hello World! 13813

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

 

Other word but more fun  is word CRASH .It will crash oracle process for testing , so be carefull.

Do not crash wrong session.

examples:

SQL> alter session set events ‘deadlock crash’;

Session altered.

 Crash someone session:

SQL>  oradebug setospid 19779
Oracle pid: 36, Unix process pid: 19779, image:
SQL> oradebug event immediate crash
ORA-00072: process “Unix process pid: 19779, image:  is not active

Crash your own session:

SQL> alter session set events ‘immediate crash’;
alter session set events ‘immediate crash’
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 13799
Session ID: 107 Serial number: 7376

Crash on event:

SQL> alter session set events ‘<event_name>  crash’;

Session altered.

SQL> alter session set events ‘parse_sql_statement crash’;

Session altered.

SQL> alter session set events ’0x23E crash’;

Session altered.

 

Happy Crashing