Shulou(Shulou.com)06/01 Report--

Session 1:

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and forty eight

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 ()

.

Session 2:

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and fifty eight

SQL > alter procedure pining compile

Hang.

Session 3:

SQL > select sid from v$mystat where rownum=1

SID

-

one hundred and forty nine

SQL > drop procedure pining

Hang.

Session 4:

SQL > select sid, event,wait_class, seconds_in_wait from v$session_wait w where w.WAIT_CLASS 'Idle'

SID EVENT WAIT_CLASS SECONDS_IN_WAIT

--

146 SQL*Net message to client Network 0

149 library cache lock Concurrency 3

158 library cache pin Concurrency 6

SQL > oradebug setmypid

Statement processed.

SQL > oradebug hanganalyze 3

Hang Analysis in/ u01/app/oracle/admin/orcl/udump/orcl_ora_16916.trc

SQL > oradebug close_trace

Statement processed.

Session 5:

SQL > oradebug setmypid

Statement processed.

SQL > oradebug unlimit

Statement processed.

SQL > oradebug dump systemstate 266

Statement processed.

SQL > oradebug tracefile_name

/ u01/app/oracle/admin/orcl/udump/orcl_ora_17000.trc

SQL > oradebug close_trace

Statement processed.

SQL >

-- View the trace file of hang:

[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 purl 0824.320

* SESSION ID: (146.17) 2017-11-23 1514 0824.320

* * 2017-11-23 15 purl 08purl 24.320

=

HANG ANALYSIS:

=

Open chains found:

-- from here on, the following session is blocked by the previous session.

Chain 1::

--

--

Other chains found:

The session below is also blocked by the front and indirectly blocked.

Chain 2::

Chain 3::

Chain 4::

Chain 5::

Chain 6::

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 Fringe 08purl 44.057

Cannot find symbol

Cannot find symbol

Cannot find symbol

[root@rhel ~] #

Orcl_ora_17000.txt

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.