Oracle Database Experts

First, login using the -prelim option of SQL*Plus as "/ as sysdba".

Then run the following:

alter session set tracefile_identifier='sysstatedump';
oradebug setmypid
oradebug unlimit

prompt Issuing command:  oradebug -g all dump systemstate 10
oradebug -g all dump systemstate 10
prompt Sleeping for 1 minute
!sleep 60
oradebug -g all dump systemstate 10

prompt We may not be able to do dump of locks
prompt Issuing command:  oradebug -g all dump locks 8
prompt If the sqlplus session hangs here for more than several minutes
prompt    kill it by hitting control-c
oradebug -g all dump locks 8
prompt Sleeping for 1 minute
!sleep 60
oradebug -g all dump locks 8

Posted by Charles Kim, Oracle ACE