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

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links