DB - Monitor, Locks

E a tal query que nao termina

And about that query that never ends ...

--
--  General
--

set lines 240
col message form a100
col opname form a50

select * from
(
 select
 round(l.sofar/l.totalwork*100,2) as complete,
 --l.*
 l.sid,
 --l.opname,
 l.message,
 l.start_time,
 l.time_remaining/60 "minuts remaining",
 l.elapsed_seconds
 from
 gv$session_longops l
 where totalwork !=0
)
where complete < 100;

--
--  RMAN Specific
--

select sid, serial#,opname, context, sofar, totalwork,
       round(sofar/totalwork*100,2) "%_complete"
from v$session_longops
where
  lower(opname) like 'rman%'
  and opname not like '%aggregate%'
  and totalwork != 0
  and sofar <> totalwork;

select
   to_char(start_time,'dd-mon-yy hh24:mi') "backup started",
   opname,
   sofar,
   totalwork,
   elapsed_seconds/60 "elapse (min)",
   round(sofar/totalwork*100,2) "complete%"
from v$session_longops
where
  lower(opname) like 'rman%'
  and opname not like '%aggregate%'
  and totalwork != 0
  and sofar <> totalwork;

 Como identificar codigo em uso

EN : How to identify code currently in use

select a.sid, a.username,a.program, b.owner, b.object, b.type
 from   v$session a, v$access b
 where  a.sid = b.sid and object like 'ITEM_MASTER'

select  a.*, s.* 
from gv$access a, gv$session s 
where a.sid = s.sid and a.owner = 'RMS'

Identificar sessoes bloquiadas

select
s1.status,
s1.logon_time,
s1.last_call_et seconds_since_active,
s1.module,
s1.username || '@' || s1.machine || ' with ' || s1.PROGRAM || ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' || ' conn with ' || s2.PROGRAM AS blocking_status,
'alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' ||  S1.INST_ID || ''' immediate ;' as kill_sess,
'alter system disconnect session ''' || s1.sid || ',' || s1.serial# || ',@' ||  S1.INST_ID || ''' immediate ;' as disconnect_sess
from
    gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
where
s1.type='USER'
and s1.status='INACTIVE'
--and S1.MODULE like 'HH%'
and s1.sid=l1.sid
and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by 3 desc;

select
s1.status,
s1.logon_time,
s1.last_call_et seconds_since_active,
s1.module,
s1.username || '@' || s1.machine || ' with ' || s1.PROGRAM || ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' || ' conn with ' || s2.PROGRAM AS blocking_status,
'alter system kill session ''' || s1.sid || ',' || s1.serial# || ',@' ||  S1.INST_ID || ''' immediate ;' as kill_sess,
'alter system disconnect session ''' || s1.sid || ',' || s1.serial# || ',@' ||  S1.INST_ID || ''' immediate ;' as disconnect_sess
from
    gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
where
s1.type='USER'
and s1.status='INACTIVE'
--and S1.MODULE like 'HH%'
and s1.sid=l1.sid
and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
order by 3 desc;

Identificar Objectos Bloqueados

select
       b.session_id as sid,
       username,
       a.owner as object_owner,
       a.object_name,
       decode(b.locked_mode, 0, 'none',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) as locked_mode,
       b.os_user_name,
       s.username,
       s.program,
       s.module,
       'alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate ;' as kill_sess
FROM   dba_objects a,
       gv$locked_object b,
       gv$session s
WHERE
a.object_id = b.object_id and b.session_id=s.sid
and s.SID in (
select
 s1.sid
from
    gv$lock l1,
    gv$session s1,
    gv$lock l2,
    gv$session s2
where
s1.type='USER'
and s1.status='INACTIVE'
and S1.MODULE like 'HH%'
and s1.sid=l1.sid
and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 )

Gerar comando para matar sessoes

select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate ;' as kill_sess,
       b.session_id as sid,
       nvl(b.oracle_username, '(oracle)') as username,
       a.owner as object_owner,
       a.object_name,
       decode(b.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             b.locked_mode) as locked_mode,
       b.os_user_name,
       s.username
from   dba_objects a,
       gv$locked_object b,
       gv$session s
where
a.object_id = b.object_id and b.session_id=s.sid
and s.sid=1234
order by 1, 2, 3, 4;