Difference between pages "Check Temp Tablespace Usage" and "Check Oracle Blocking Locks"

From NazimWIKI
(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 distinct s.username
+
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.serial#
+
     , s.username
 +
    , s.status
 +
    , s.schemaname
 
     , s.osuser
 
     , s.osuser
     , u.tablespace
+
     , s.process
     , u.contents
+
     , s.machine
     , u.segtype
+
     , s.terminal
     , u.extents
+
     , s.program
     , u.blocks
+
     , s.sql_hash_value
 +
    , s.logon_time
 +
    , p.spid
 +
    , l.id2
 +
    , l.ctime
 
   FROM v$session s
 
   FROM v$session s
     , v$sort_usage u
+
     , v$process p
  WHERE s.saddr=u.session_addr
+
    , v$lock l
order by s.username, s.osuser;
+
    , 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;