Analiza Data Dictionary Views

(Analiza sačinjena dok se prvi proces čeka iz primera - DeadLockCRVENOICrno)

Analiza Data Dictionary Views. 1

V$LOCK.. 1

DBA_BLOCKERS. 1

DBA_WAITERS. 2

DBA_DML_LOCKS. 2

DBA_LOCK.. 2

V$LOCKED_OBJECT. 3

V$SESSION_WAIT. 3

V$LOCK

SQL> select * from v$lock where block=1;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK

-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------

333B2B64 333B2C80 159 TX 196639 2567 6 0 1233 1

DBA_BLOCKERS

SQL> select * from DBA_BLOCKERS;

HOLDING_SESSION

---------------

159

DBA_WAITERS

SQL> select * from DBA_WAITERS;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2

--------------- ---------------- --------- ---------- ---------- --------------- --------------- -------------------------- -----------------

137 159 Transaction Exclusive Exclusive 196639 2567

DBA_DML_LOCKS

SQL> select * from DBA_DML_LOCKS;

SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT

---------- ------------------------------ ------------------------------ ------------- ------------- ------------

BLOCKING_OTHERS

----------------------------------------

137 USER_TS2 TABLE1 Row-X (SX) None 1420

Not Blocking

159 USER_TS2 TABLE1 Row-X (SX) None 1411

Not Blocking

DBA_LOCK

SQL> select * from DBA_LOCK where BLOCKING_OTHERS='Blocking';

SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUESTED

---------- -------------------------- ---------------------------------------- ---------------

LOCK_ID1 LOCK_ID2 LAST_CONVERT

---------------------------------------- ---------------------------------------- ------------

BLOCKING_OTHERS

----------------------------------------

159 Transaction Exclusive None

196639 2567 1191

V$LOCKED_OBJECT

SQL>select * from V$LOCKED_OBJECT

/

XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME

---------- ---------- ---------- ---------- ---------- ------------------------------ -----------------------

PROCESS LOCKED_MODE

------------ -----------

7 39 1991 53901 137 USER_TS2 ORA10GFS\Administrator

260:2188 3

3 31 2567 53901 159 USER_TS2 ORA10GFS\Administrator

2196:3256 3

V$SESSION_WAIT

SQL> select * from V$SESSION_WAIT where sid=137 or sid=159

/

SID SEQ# EVENT

---------- ---------- ----------------------------------------------------------------

P1TEXT P1 P1RAW

---------------------------------------------------------------- ---------- --------

P2TEXT P2 P2RAW

---------------------------------------------------------------- ---------- --------

P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS#

---------------------------------------------------------------- ---------- -------- ------------- -----------

WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE

---------------------------------------------------------------- ---------- --------------- -------------------

137 38 enq: TX - row lock contention

name|mode 1415053318 54580006

usn<<16 | slot 196639 0003001F

sequence 2567 00000A07 4217450380 1

Application 0 2136 WAITING

159 68 SQL*Net message from client

driver id 1111838976 42455100

#bytes 1 00000001

0 00 2723168908 6

Idle 0 2877 WAITING