SQL> create or replace procedure pining is begin null; end; / Procedure created SQL> SQL> create or replace procedure calling is begin pining; dbms_lock.sleep(200); end; / SQL> SQL> call calling(); .................
--查看hang的trace文件: [root@rhel ~]# cat /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0.1/db_1 System name: Linux Node name: rhel Release: 2.6.18-238.el5 Version: #1 SMP Sun Dec 19 14:22:44 EST 2010 Machine: x86_64 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 20 Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)
*** SERVICE NAME:(SYS$USERS) 2017-11-23 15:08:24.320 *** SESSION ID:(146.17) 2017-11-23 15:08:24.320 *** 2017-11-23 15:08:24.320 ============== HANG ANALYSIS: ============== Open chains found: --從這里開始以下的session都是被前面的session阻塞 Chain 1 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/148/102/0x83a5dcc0/16769/PL/SQL lock timer> -- <0/158/250/0x83a5e4a8/16912/library cache pin> -- <0/149/135/0x83a5ec90/16914/library cache lock> Other chains found: --下面的session也是被前面所阻塞,被間接阻塞 Chain 2 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/140/1/0x83a62bd0/2825/Streams AQ: qmn slave idle wait> Chain 3 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/146/17/0x83a5f478/16916/No Wait> Chain 4 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/147/2/0x83a623e8/2823/Streams AQ: waiting for time man> Chain 5 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/151/987/0x83a5ccf0/17003/jobq slave wait> Chain 6 : <cnode/sid/sess_srno/proc_ptr/ospid/wait_event> : <0/154/1/0x83a5d4d8/2797/Streams AQ: qmn coordinator idle> Extra information that will be dumped at higher levels: [level 4] : 1 node dumps -- [REMOTE_WT] [LEAF] [LEAF_NW] [level 5] : 5 node dumps -- [SINGLE_NODE] [SINGLE_NODE_NW] [IGN_DMP] [level 6] : 2 node dumps -- [NLEAF] [level 10] : 12 node dumps -- [IGN] State of nodes ([nodenum]/cnode/sid/sess_srno/session/ospid/state/start/finish/[adjlist]/predecessor): [139]/0/140/1/0x83b58c18/2825/SINGLE_NODE/1/2//none [144]/0/145/12/0x83b5f130/17000/IGN/3/4//none [145]/0/146/17/0x83b60568/16916/SINGLE_NODE_NW/5/6//none [146]/0/147/2/0x83b619a0/2823/SINGLE_NODE/7/8//none [147]/0/148/102/0x83b62dd8/16769/LEAF/9/10//157 [148]/0/149/135/0x83b64210/16914/NLEAF/11/14/[157]/none [150]/0/151/987/0x83b66a80/17003/SINGLE_NODE/15/16//none [153]/0/154/1/0x83b6a728/2797/SINGLE_NODE/17/18//none [157]/0/158/250/0x83b6f808/16912/NLEAF/12/13/[147]/148 [159]/0/160/1/0x83b72078/2789/IGN/19/20//none [160]/0/161/1/0x83b734b0/2787/IGN/21/22//none [161]/0/162/1/0x83b748e8/2785/IGN/23/24//none [162]/0/163/1/0x83b75d20/2783/IGN/25/26//none [163]/0/164/1/0x83b77158/2781/IGN/27/28//none [164]/0/165/1/0x83b78590/2779/IGN/29/30//none [165]/0/166/1/0x83b799c8/2777/IGN/31/32//none [166]/0/167/1/0x83b7ae00/2775/IGN/33/34//none [167]/0/168/1/0x83b7c238/2773/IGN/35/36//none [168]/0/169/1/0x83b7d670/2771/IGN/37/38//none [169]/0/170/1/0x83b7eaa8/2769/IGN/39/40//none ==================== END OF HANG ANALYSIS ==================== /u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /u01/app/oracle/product/10.2.0.1/db_1 System name: Linux Node name: rhel Release: 2.6.18-238.el5 Version: #1 SMP Sun Dec 19 14:22:44 EST 2010 Machine: x86_64 Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 20 Unix process pid: 16916, image: oracle@rhel (TNS V1-V3)
*** 2017-11-23 15:08:44.057 Cannot find symbol Cannot find symbol Cannot find symbol [root@rhel ~]#
orcl_ora_17000.txt
