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

-- find sid and serial#

SELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.OBJECT_ID = o.OBJECT_ID

AND l.session_id = s.sid

SID SERIAL# ORACLE_USERNAME

OS_USER_NAME

-

OBJECT_NAME

LOCKED_MODE

-

125 199 SYS

Oracle

TESTLOCK

three

SID SERIAL# ORACLE_USERNAME

OS_USER_NAME

-

OBJECT_NAME

LOCKED_MODE

-

50 181 SYS

Oracle

TESTLOCK

three

-- release SESSION SQL:

Alter system kill session '50128'

-- find the SQLid of the lock

SQL > select sid,event,sql_id from v$session where sid in ('125pm 50')

SID EVENT

-

SQL_ID

-

50 enq: TX-row lock contention

3c8u4r1z8u9ss

125 SQL*Net message from client

Fvk2xw29t6hw4

-- View the execution plan of SQL

SQL > select * from table (dbms_xplan.displaY_cursor ('3c8u4r1z8u9ss'))

PLAN_TABLE_OUTPUT

SQL_ID 3c8u4r1z8u9ss, child number 0

-

Update testlock set num=8 where num=7

Plan hash value: 2631153153

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | UPDATE STATEMENT | 2 (100) | |

| | 1 | UPDATE | TESTLOCK |

PLAN_TABLE_OUTPUT

| | * 2 | TABLE ACCESS FULL | TESTLOCK | 1 | 13 | 2 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-filter ("NUM" = 7)

Note

-

-dynamic statistics used: dynamic sampling (level=2)

PLAN_TABLE_OUTPUT

23 rows selected.

-- find the statement of SQL

Select sql_text from v$sqltext where SQL_ID='3c8u4r1z8u9ss'

--

SQL > 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

-

125 TX 655382 969 6 0 9699 1

50 TX 655382 969 0 6 9622 0

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.