Cristiano,
Eu uso este:
--Informe um dos parâmetros para restringir ou deixe tudo vazio (para listar
todos)
set lines 100 pages 100 verify off;
column sid format a10;
column serial format a10;
prompt
accept WOBJ prompt 'Informe nome do OBJETO ou <ENTER> para todos: '
accept Wsid prompt 'Informe SID ou <ENTER> para todas: '
accept Wmodule prompt 'Informe MODULE ou <ENTER> para todos: '
select vs.audsid audsid,
to_char(locks.sid) sid,
to_char(vs.serial#) serial,
vs.username oracle_user,
vs.osuser os_user,
vs.program program,
vs.module module,
vs.action action,
vs.terminal,
vs.process process,
objs.owner object_owner,
objs.object_name object_name,
objs.object_type object_type
, round( locks.ctime/60, 2 ) lock_time_in_minutes
from gv$session vs,
gv$lock locks,
all_objects objs,
all_tables tbls
where locks.id1 = objs.object_id
and vs.sid = locks.sid
and (vs.sid ='&Wsid' OR nvl('&Wsid',0) =0)
and objs.owner = tbls.owner
and objs.object_name = tbls.table_name
and objs.owner != 'SYS'
and locks.type = 'TM'
and (vs.module = '&Wmodule' or nvl('&Wmodule','.') ='.')
and (objs.object_name ='&WOBJ' OR nvl('&WOBJ','.') ='.')
group by vs.audsid, locks.sid, vs.serial#, vs.username, vs.osuser, vs.program,
vs.module, vs.action, vs.terminal, vs.process, objs.owner,
objs.object_name, objs.object_type, round( locks.ctime/60, 2 )
order by lock_time_in_minutes;
set lines 100 pages 20 verify on;
Ederson Elias
DBA Oracle - http://br.linkedin.com/pub/ederson-elias/24/8b/8b0
------------ Labor improbus omnia vincit
