Skip to content
March 18, 2014 / doganay

LIST OPEN TRANSACTIONS ON A TABLE

accept TABLE_NAME prompt "TABLE NAME.....: "
select /*+ RULE */
'@kill '||s.sid||','||s.serial# sidserial,
rn.usn "Rollback segment number",
rn.name "Rollback segment name",
s.schemaname "User",
o.name "Table Name"
from
v$rollname rn,
v$session s,
sys.obj$ o,
v$lock lck1,
v$transaction t
where
s.taddr = t.addr
and lck1.sid = s.sid
and lck1.id1 = o.obj#
and lck1.type = 'TM'
and t.xidusn = rn.usn
and o.name=upper('&&TABLE_NAME')
/

Thx Ramazan 🙂

http://r-ozturk.blogspot.com.tr/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: