1. 這是個初學農友的農友日誌~ 2. Oracle ERP 分享 3. Oracle DBA 心得 4. Android 開發心得 5. MoJo寵物手作粉絲成員 6. 程式客製及外包專案接案
2012年12月24日 星期一
Oracle DBA - Query locked database objects
SELECT a.username, a.sid, a.objname1,objname2, SYSDATE current_time, TYPE lock_type, LMODE,
A.LOGON_TIME
FROM (SELECT sn.sql_address,
sn.sql_hash_value,
sn.username,
m.sid,
DECODE(m.type,
'TM','TM-DML enqueue',
'TX','TX-Transaction enqueue',
'UL','User supplied') type,
DECODE(m.lmode,
0,'None',
1,'Null(NULL)',
2,'Row Share(SS)',
3,'Row Excl.(SX)',
4,'Share(S)',
5,'S/Row Excl.(SSX)',
6,'Exclusive(X)',
lmode,ltrim(to_char(lmode, '990'))) lmode,
DECODE(m.request,
0,'None',
1,'Null(NULL)',
2,'Row Share(SS)',
3,'Row Excl.(SX)',
4,'Share(S)',
5,'S/Row Excl.(SSX)',
6,'Exclusive(X)',
request,ltrim(to_char(request, '990'))) request,
obj1.owner || '.' || obj1.object_name objname1,
obj2.owner || '.' || obj2.object_name objname2,
SN.LOGON_TIME
FROM v$session sn, V$lock m, dba_objects obj1, dba_objects obj2
WHERE sn.sid = m.sid
AND m.id1 = obj1.object_id(+)
AND m.id2 = obj2.object_id(+)
AND m.lmode != 4) a
WHERE username is not null
and a.objname1 <> '.'
ORDER BY 3, 1;
訂閱:
張貼留言 (Atom)
沒有留言:
張貼留言