ALL DBA’s Desktop Sql’s . (2-Database locks.sql)
Oracle Data Concurrency ve Consistency i sağlamak amacıyla DML, DDL lock ları geliştirmiştir. Db oluşan bir lock i görüntüleyebilmek için aşagıdaki sql lerden faydalanırız.
- DML locks single instance. TX row locks.. ( TX ‘i TM ifadesi ile değiştirerek Table lock ları görebiliriz) .
SELECT
l1.SID blocker, l1.ctime block_time, l2.SID waiter,
l2.ctime wait_time
FROM v$lock l1, v$lock l2
WHERE l1.TYPE = ‘TX’
AND l1.BLOCK = 1
AND l2.TYPE = ‘TX’
AND l2.id1 = l1.id1
AND l2.id2 = l1.id2
AND l2.SID != l1.SID
ORDER BY l1.SID, l2.SID ;
- 2 node lu RAC database için kullanılabilecek Sql sorgusu
SELECT DECODE(G.INST_ID,1,’INSTANCE1′,2,’INSTANCE2′) INSTANCE,
S.SID,
G.TYPE,
S.USERNAME,
S.SERIAL#,
S.PROCESS,
DECODE(LMODE,0,’None’,1,’Null’,2,’Row-S’,3,’Row-X’,4,’Share’,5,’S/ROW’, 6,’Exclusive’) LMODE,
DECODE(REQUEST,0,’None’,1,’Null’,2,’Row-S’,3,’Row-X’,4,’Share’, 5,’S/ROW’,6,’Exclusive’)REQUEST,
DECODE(REQUEST,0,’BLOCKER’,'WAITER’) STATE
FROM GV$GLOBAL_BLOCKED_LOCKS G,
GV$SESSION S
WHERE G.SID = S.SID
AND G.INST_ID = S.INST_ID
ORDER BY STATE ;
- Hangi session ın block ladığını görebilmek için ;
SQL> select s1.username || ‘@’ || s1.machine
2 || ‘ ( SID=’ || s1.sid || ‘ ) is blocking ‘
3 || s2.username || ‘@’ || s2.machine || ‘ ( SID=’ || s2.sid || ‘ ) ‘ AS blocking_status
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;
SQL> select l1.sid, ‘ IS BLOCKING ‘, l2.sid
2 from v$lock l1, v$lock l2
3 where l1.block =1 and l2.request > 0
4 and l1.id1=l2.id1
5 and l1.id2=l2.id2
