chk_lockobj.sql

— To check lock objects

set linesize 200
set pagesize 80

col inst_id format 999
col sid format 99999
col serial# format 999999
col username format a15
col machine format a15
col status format a12
col sql_id format a15
col prev_sql_id format a15
col object_name format a30
col object_id fromat 9999999
select b.inst_id,
b.sid,
b.serial#,
b.username,
b.machine,
b.status,
b.sql_id,
— b.prev_sql_id,
— c.sql_text,
— d.object_id,
e.object_name,
a.start_time,
b.logon_time
from  gv$transaction a,
gv$session b,
gv$sql c,
v$locked_object d,
all_objects e
where a.inst_id = b.inst_id
and a.ses_addr = b.SADDR
and b.prev_sql_addr = c.address(+)
and b.prev_hash_value = c.hash_value(+)
and b.prev_child_number = c.child_number(+)
and b.inst_id = c.inst_id(+)
and b.prev_sql_id=c.sql_id
and d.object_id=e.object_id
and d.session_id=b.sid(+)
/

Leave a Reply

Your email address will not be published. Required fields are marked *