Difference between pages "Check Temp Tablespace Usage" and "Check Oracle Blocking Locks"
(Difference between pages)
Jump to navigation
Jump to search
(Created page with "<blockquote><pre> SELECT distinct s.username , s.sid , s.serial# , s.osuser , u.tablespace , u.contents , u.segtype , u.extents , u.blo...") |
(Created page with "<blockquote><pre> SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi') dttm , ' --- BLOCKER --- ' , lo.session_id , lo.process , lo.os_user_name , dob.object...") |
||
Line 1: | Line 1: | ||
<blockquote><pre> | <blockquote><pre> | ||
− | SELECT | + | SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi') dttm |
+ | , ' --- BLOCKER --- ' | ||
+ | , lo.session_id | ||
+ | , lo.process | ||
+ | , lo.os_user_name | ||
+ | , dob.object_name | ||
+ | , dob.object_type | ||
+ | , ' --- BLOCKING --- ' | ||
, s.sid | , s.sid | ||
− | , s. | + | , s.username |
+ | , s.status | ||
+ | , s.schemaname | ||
, s.osuser | , s.osuser | ||
− | , | + | , s.process |
− | , | + | , s.machine |
− | , | + | , s.terminal |
− | , | + | , s.program |
− | , | + | , s.sql_hash_value |
+ | , s.logon_time | ||
+ | , p.spid | ||
+ | , l.id2 | ||
+ | , l.ctime | ||
FROM v$session s | FROM v$session s | ||
− | , v$ | + | , v$process p |
− | WHERE s. | + | , v$lock l |
− | + | , v$locked_object lo | |
+ | , dba_objects dob | ||
+ | WHERE s.lockwait is not null | ||
+ | AND p.addr(+) = s.paddr | ||
+ | AND l.sid = s.sid | ||
+ | AND l.type='TX' | ||
+ | AND l.id2 = lo.xidsqn | ||
+ | AND lo.object_id = dob.object_id; | ||
</pre></blockquote> | </pre></blockquote> |
Latest revision as of 22:53, 8 November 2019
SELECT to_char(sysdate,'yyyy-mm-dd hh24:mi') dttm , ' --- BLOCKER --- ' , lo.session_id , lo.process , lo.os_user_name , dob.object_name , dob.object_type , ' --- BLOCKING --- ' , s.sid , s.username , s.status , s.schemaname , s.osuser , s.process , s.machine , s.terminal , s.program , s.sql_hash_value , s.logon_time , p.spid , l.id2 , l.ctime FROM v$session s , v$process p , v$lock l , v$locked_object lo , dba_objects dob WHERE s.lockwait is not null AND p.addr(+) = s.paddr AND l.sid = s.sid AND l.type='TX' AND l.id2 = lo.xidsqn AND lo.object_id = dob.object_id;