SQL Script Monitor SQL Text And Locks

Updated : 18-Mar-2002
Version : 1.2

Description

This script reports the SQL text of locks currently being held in the database.

Parameters

None

SQL Source

set pagesize 66 linesize 132
set echo on

spool sqltext.lst

column sql_text        heading "SQL Text"   format a60 wrap
column object          heading "Object"   format a25 wrap
column username        heading "User"   format a10 wrap

select s.username username,  
       a.sid sid,  
       a.owner||'.'||a.object object,  
       s.lockwait,  
       t.sql_text sql_text 
from   v$sqltext t,  
       v$session s,  
       v$access a 
where  t.address=s.sql_address  
and    t.hash_value=s.sql_hash_value  
and    s.sid = a.sid  
and    a.owner != 'SYS' 
and    upper(substr(a.object,1,2)) != 'V$' 
order by t.piece
/

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal