Oracle oradebug 命令 使用说明
当数据库出现一些挂起状态时，如果sqlplus仍然可以连接，可能视图查询没有相应，但是可以通过oradebug工具来进行进程及系统状态信息的转储，从而可以进行Hang分析。
DUMP进程状态可以使用：
alter sessions set events 'immediate trace name processstate level <level>'；
或者使用：
oradebug setmypid
oradebug ulimit
oradebug dump processstate<level>
当诊断数据库挂起条件时可以DUMP系统状态：
alter sessions set events 'immediate trace name systemstate level <level>'；
或：
oradebug setmypid
oradebug ulimit
oradebug dump systemstate <level>-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: Delete22: 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 StateEnqueue 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阻塞?
内容是网上粘贴的，但我按照步骤拿生产环境做试验了。初学者，看不懂trc文件。不过用awk工具转化后，就知道结果了。不过。。