Oracle oradebug命令使用说明

当数据库出现一些挂起状态时，如果sqlplus仍然可以连接，可能视图查询没有相应，但是可以通过oradebug工具来进行进程及系统状态信息的转储，从而可以进行Hang分析。
DUMP进程状态可以使用：
　　alter sessions set events 'immediate trace name processstate level '；
或者使用：
　　oradebug setmypid
　　oradebug ulimit
　　oradebug dump processstate
当诊断数据库挂起条件时可以DUMP系统状态：
　　alter sessions set events 'immediate trace name systemstate level '；
　　或：
　　oradebug setmypid
　　oradebug ulimit
　　oradebug dump systemstate  

如果为了获取全面一点的信息，可以使用Level 10。
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10
SQL>oradebug TRACEFILE_NAME
SQL>oradebug close_trace
。可以通过awk脚本对trc进分析。（简单，直接。源文件太大，分析起来费劲）
awk-f ass109.awk/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc
Starting Systemstate 1
..................................
Ass.Awk Version 1.0.9 - Processing /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_9976.trc

System State 1
~~~~~~~~~~~~~~~~
1:                                      
2:  0: waiting for 'pmon timer'        
3:  0: waiting for 'rdbms ipc message' 
4:  0: waiting for 'VKTM Logical Idle Wait'
5:  0: waiting for 'rdbms ipc message' 
6:  0: waiting for 'DIAG idle wait'    
7:  0: waiting for 'rdbms ipc message' 
8:  0: waiting for 'DIAG idle wait'    
9:  0: waiting for 'rdbms ipc message' 
10: 0: waiting for 'rdbms ipc message' 
11: 0: waiting for 'rdbms ipc message' 
12: 0: waiting for 'rdbms ipc message' 
13: 0: waiting for 'smon timer'        
14: 0: waiting for 'rdbms ipc message' 
15: 0: waiting for 'rdbms ipc message' 
16: 0: waiting for 'rdbms ipc message' 
17:                                     
18:                                     
19: 0: waiting for 'Space Manager: slave idle wait'
20: 0: waiting for 'SQL*Net message from client'
21: 0: waiting for 'enq: TX - row lock contention'[Enqueue TX-000A0020-0000024F] 
     Cmd: Delete
22: 0: waiting for 'rdbms ipc message' 
23: 0: waiting for 'rdbms ipc message' 
24: 0: waiting for 'rdbms ipc message' 
25: 0: waiting for 'rdbms ipc message' 
26: 0: waiting for 'Streams AQ: qmn coordinator idle wait'
27:                                     
28:                                     
30: 0: waiting for 'Streams AQ: qmn slave idle wait'
31: 0: waiting for 'rdbms ipc message' 
33: 1: waited for 'Streams AQ: waiting for time management or cleanup tasks'
35: 0: waiting for 'rdbms ipc message' 
41:                                     
44:                                     
Blockers
~~~~~~~~

        Above is a list of all the processes. If they are waiting for a resource
        then it will be given in square brackets. Below is a summary of the
        waited upon resources, together with the holder of that resource.
        Notes:
        ~~~~~
         o A process id of '   ' implies that the holder was not found in the
           systemstate.

                    Resource Holder State
Enqueue TX-000A0020-0000024F    20: 0: waiting for 'SQL*Net message from client'

Object Names
~~~~~~~~~~~~
Enqueue TX-000A0020-0000024F                                                                 

30586 Lines Processed.
--从这里马上就可以知道pid 21 请求Enqueue TX被pid 20阻塞