When there is an issue with an instance or session hang run this sql
sqlplus ‘/ as sysdba’
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4 <=== Issue command 3 times one minute apart
oradebug -g all dump systemstate 267 <=== Issue command 3 times one minute apart
quit
help is available :
sql> oradebug help
Trace files will be produced in the user_dump_dest directory. They are named according to the SPID of the process where the oradebug command is executed.
SQL> sho parameter user_dump_dest NAME TYPE VALUE ------------------------------------ -------------------------------- ------------------------------ user_dump_dest string C:\oracle\diag\rdbms\xxx\xxx1\trace C:\Temp>set ORACLE_SID=xxxa1 C:\Temp>sqlplus / as sysdba SQL*Plus: Release 11.1.0.7.0 - Production on Thu Aug 26 08:43:27 2010 Copyright (c) 1982, 2008, Oracle. All rights reserved. Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option SQL> set timing on SQL> set time on 08:43:38 SQL> spool oradebug_xxxx1 08:43:40 SQL> oradebug setmypid