set WRAP off;
set pagesize 500;
set linesize 500;
prompt '================================================'
prompt 'Sessoes....'
col USERNAME format a15;
col MACHINE format a30;
col PROGRAM format a20;
col SID format 99999;
col SPID format a5;
col INST_ID format 99999;
col QUERIES format 9999;

select sess.USERNAME,sess.PROGRAM,sess.INST_ID,count(*)
from GV$SESSION sess
where sess.PROGRAM not like '%oracle@%'
and osuser != 'oracle'
group by sess.USERNAME,sess.PROGRAM,sess.INST_ID;


set linesize 500;
prompt '================================================'
prompt 'Objetos invalidos....'
select count(*),owner from dba_objects where status = 'INVALID' group by owner;


col target format a30;
col opname format a20;
select SID,SERIAL#,SQL_ID,OPNAME,TARGET,totalwork,sofar,TIME_REMAINING from gv$session_longops where totalwork!=sofar;

prompt '================================================'
prompt 'Locks…'

@?/rdbms/admin/utllockt.sql 

col ctime format 99999999;
col request format 9999999;
col object format a30;
col lobj format a30;
col owner format a15;
col sinfo format a75;

select b.*, sw.event, sw.seconds_in_wait from (
select count(*) cnt, a.* from (
select
  ctime/60 ctime,
  l.sid,
  l.type,
  o.owner owner,
  s.osuser||'@'||s.machine||'::'||s.program sinfo
from v$lock l
  left join v$locked_object lo on lo.session_id = l.sid
  left join dba_objects o on o.object_id = lo.object_id
  left join v$session s on s.sid=l.sid
where l.type in ('TX','TM') and l.ctime > 600 
) a 
group by   ctime,sid,type,owner,sinfo
order by 1 desc
) b
inner join v$session_wait sw on sw.sid = b.sid;

select 
  l.ctime/60 ctime, 
  l.sid, 
  l.type,
  l.id1,
  so.name lobj,
  l.lmode,
  l.request,
  o.owner||'.'||o.object_name object, 
  s.osuser||'@'||s.machine||'::'||s.program sinfo 
from v$lock l 
  left join v$locked_object lo on lo.session_id = l.sid
  left join dba_objects o on o.object_id = lo.object_id
  left join v$session s on s.sid=l.sid 
  left join sys.obj$ so on so.obj# = l.id1
where l.type in ('TX','TM') and l.ctime > 600 order by 1 desc;


col item_lock format a150;
col sid_ser format a12;
col ctime format 999999;
col pid format a10;
col db_user format a10;
col os_user format a16;
col object format a10;
col lockmode format a10;
col sid_ser format a12;
col machine format a20;
col sql_id format a16;
col program format a24;
col sid format a10;

SELECT /*+ RULE */ DISTINCT ''''||s.sid||','||s.serial#||'''' SID_SER,
CTIMe/60 CTime,
pr.spid pid, 
NVL(b.oracle_username, '(oracle)') db_user,
a.owner||'.'||a.object_name object,
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) lockmode,
s.machine,
s.osuser os_user,
s.program,
s.sql_id
     FROM   dba_objects a,
             v$locked_object b,
             v$lock l,
             v$session s,
             v$process pr
     WHERE  a.object_id = b.object_id
     AND l.sid = b.session_id
     AND s.PADDR=pr.ADDR
     AND s.sid = l.sid
     AND ctime > 600
     order by ctime desc;


select lc.SESSION_ID,lc.PROCESS,lc.ORACLE_USERNAME db_user,o.OBJECT_name
from v$locked_object lc, dba_objects o where lc.object_id = o.object_id;

prompt '================================================'
prompt 'TOP 10 Maquinas ativas'
col machine format a25;
col queries  format 9999;
select * from (
select sess.machine,count(*) queries
     from      gv$session sess
     left join gv$sql sql on ( sess.sql_id = sql.sql_id )
     where PROGRAM = 'Tasy.exe' and lower(osuser) not like '%oracle%'
group by sess.machine
order by count(*) desc )
where rownum < 10;


prompt '================================================'
prompt 'Sessões Ativas'

select count(*),inst_id from gv$session group by inst_id;
exit;

