DBA query with kill commands:
SELECT b.inst_id||’ ‘||c.object_type||’ ‘||c.owner||’.’||c.object_name||’
‘||c.subobject_name||
‘ user:’||b.username||’ ‘||b.osuser||’ on ‘||b.machine||’ ‘||b.status||’
‘||b.sid||’,’||b.serial# “Instance Object Session Info”,
‘alter system kill session ”’||b.sid||’,’||b.serial#||”’;’ kill_cmd
FROM gv$locked_object a JOIN gv$session b ON (a.inst_id = b.inst_id AND
b.sid = a.session_id) JOIN dba_objects c ON (a.object_id = c.object_id)
ORDER BY c.owner,c.object_name,c.subobject_name,b.inst_id,b.machine;
User accessible view:
CREATE OR REPLACE VIEW http://sys.my/ sys.my_locked_objects AS
SELECT b.inst_id,c.object_type,c.owner || ‘.’ || c.object_name
OBJECT,c.subobject_name,b.username,b.osuser,b.machine,b.status,b.sid,b.ser
ial#
FROM gv$locked_object a JOIN gv$session b ON (a.inst_id = b.inst_id AND
b.sid = a.session_id) JOIN dba_objects c ON (a.object_id = c.object_id)
WHERE c.owner = USER OR b.username = USER;
You can grant select on this view to PUBLIC and make a public synonym if you wish.