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

Experiment:

Session 1:

SQL > show user

USER is "SYS"

SQL >

SQL > create table t_all_objs as select owner,object_id,object_name from all_objects where 0room1

Table created.

SQL > alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID)

Table altered.

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011701,'test1')

1 row created.

SQL > insert into t_all_objs (owner,object_id,object_name) values ('TEST',2013011702,'test2')

1 row created.

SQL > commit

Commit complete.

SQL > select sid from v$mystat where rownum

SQL > select * from t_all_objs

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test1

TEST 2013011702 test2

SQL > update t_all_objs set object_name='test11' where object_id=2013011701

1 row updated.

Not submitted; not submitted.

Session 2:

SQL > update t_all_objs set object_name='test101' where object_id=2013011701

1 row updated.

Hang lives in..

-- there is obviously blocking. Assuming that we only know that the blocked object is the T_ALL_OBJS table, the troubleshooting is as follows, mainly based on the ROW_WAIT_OBJ#, that associates object_id with v$session as follows:

Set lines 200 pages 999

Col ORACLE_USERNAME for a14

Col OBJECT_NAME for a20

Col MACHINE for a14

Col OS_USER_NAME for a14

Col terminal for a14

Select l.session_id sid

S.serial#

L.locked_mode

L.oracle_username

L.os_user_name

S.machine

S.terminal

O.object_name

O.object_type

O.object_id

S.logon_time

From v$locked_object l, dba_objects o, v$session s

Where l.object_id = o.object_id

And o. Objectless nameplate object all object OBJS'

And l.session_id = s.sid

Order by sid, s.serial#

SID SERIAL# LOCKED_MODE ORACLE_USERNAM OS_USER_NAME MACHINE TERMINAL OBJECT_NAME OBJECT_TYPE OBJECT_ID LOGON_TIME

59 165 3 SYS oracle wang pts/9 T_ALL_OBJS TABLE 89985 06-NOV-17

61 721 3 SYS oracle wang pts/8 T_ALL_OBJS TABLE 89985 06-NOV-17

Or directly query the object_ id value of dba_object.

Then it is associated with ROW_WAIT_OBJ#=89985, or ROW_WAIT_OBJ#=object_id.

SQL > select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX-row lock contention' and ROW_WAIT_OBJ#=89985

SID SQL_ID STATUS BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

--

59 c53uad8st2u8t ACTIVE 61 89985 1 102393 0

-- then according to blocking_seesin=61, query:

SQL > select sid,serial#,sql_id,status,event,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=61

SID SERIAL# SQL_ID STATUS EVENT BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#

- --

61 721 INACTIVE SQL*Net message from client-1 000

-- find the cause, sid, and kill:

SQL > alter system kill session '61721' immediate

System altered.

SQL >

-- found that session 2 has been submitted

SQL > update t_all_objs set object_name='test101' where object_id=2013011701

1 row updated.

-- query after commit submission

SQL > commit

Commit complete.

SQL > select * from t_all_objs

OWNER OBJECT_ID OBJECT_NAME

-

TEST 2013011701 test101

TEST 2013011702 test2

SQL >

=

Or directly use the following three methods to investigate:

Select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock where block=1 or request0

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-

59 TX 393249 10702 0 6 127 0

61 TX 393249 10702 6 0 135 1

Select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime

From v$lock a, v$lock b

Where a.id1 = b.id1

And a.id2 = b.id2

And a.block = 1

And b.block = 0

HOLD_SID WAIT_SID TY ID1 ID2 CTIME

-

61 59 TX 393249 10702 108

Select decode (request,0,'holder:', 'waiter:') | |

Sid session_id, id1, id2, lmode, request, type

From v$lock

Where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)

Order by id1, request

SESSION_ID ID1 ID2 LMODE REQUEST TY

-

Holder: 61 393249 10702 6 0 TX

Waiter: 59 393249 10702 0 6 TX

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.