ALTER SESSION SET nls_date_format='dd/mm/yy hh24:mi';
SET lines 200 trims on
SET pagesize 5000
col username for a15
col osuser          for a15
col machine for a30
col client_info for a20
col module          for a20
col logon_time      for a16
SELECT       p.spid as "ID_SO", 
       s.sid as "ID_BD", 
       s.serial#, 
       s.username, 
       s.osuser, 
       s.machine, 
       s.client_info, 
       s.module, 
       s.status,
--     s.inst_id,
       s.logon_time
             FROM   gv$session  s,
                           v$process   p
             WHERE  s.paddr = p.addr
                 AND s.status='ACTIVE'
             --     AND s.sid=18
             --     AND p.spid=1672
             --     AND    s.username in ('M554670','M5568714','ADMGID')
/



/* ===========> SESSÕES ATIVAS   - ( RAC )  <=========== */

TTITLE SKIP 1 "SESSÕES AMBIENTE RAC" SKIP 2 
SET LINES 250 PAGES 1000
CLEAR COLUMNS
COLUMN log_time FORMAT a19 
COLUMN sid      FORMAT 9999 
COLUMN username FORMAT a15 
COLUMN osuser   FORMAT a20 
COLUMN serial#  FORMAT 99999
COLUMN spid     FORMAT a10
COLUMN lockwait FORMAT 99999
COLUMN MODULE   format a10 trunc
COLUMN LAST_CALL_ET   format 9999
COLUMN action   format a10 trunc
COLUMN St       FORMAT a2
COLUMN cx       FORMAT a2
COLUMN machine  FORMAT a10 TRUNC
COLUMN program  FORMAT a10 TRUNC
SELECT * 
  FROM (SELECT a.sid, 
               a.serial#,
               a.username,
               SUBSTR(TO_CHAR(a.logon_time,' dd/mm/yy hh24:mi:ss'),1,20) log_time,
               a.process,
               a.osuser, 
               b.spid,                        
               SUBSTR(a.status,1,2) st, 
               a.lockwait,
			   a.MODULE, a.action, a.LAST_CALL_ET/60 LAST_CALL,
               DECODE(a.server,'NONE','',SUBSTR(a.server,1,2)) cx, 
               a.machine,
               a.program,
               a.ROW_WAIT_BLOCK# 
        FROM   gv$session a, gv$process b
        WHERE  a.paddr = b.addr
		--AND a.username = 'TS_CONSULTA'		
        AND a.sid = 150
		--AND b.spid = 15281
        --AND  a.lockwait IS NOT NULL 
         --AND SUBSTR(a.status,1,1) = 'A'
        ORDER BY 4 DESC);
        --ORDER BY 4 ASC);
TTITLE OFF;

/* -- GERA COMANDO PARA MATAR AS SESSOES EM LOCK -- */
        -- AND SUBSTR(a.status,1,1) = 'I'

/*-- LISTA SESSOES QUE ESTÃO BLOQUEANDO AS SESSOES -- */

SET lines 300
SET pages 1000
COLUMN blocker format a25
COLUMN blockee format a25
COLUMN sid_serial format a10
SELECT
 (SELECT username || ' - ' || osuser FROM v$session WHERE sid=a.sid) blocker, a.sid || ', ' ||
 (SELECT serial# FROM v$session WHERE sid=a.sid) sid_serial,' Está Bloqueando ',
 (SELECT username || ' - ' || osuser FROM v$session WHERE sid=b.sid) blockee, b.sid || ', ' ||
 (SELECT serial# FROM v$session WHERE sid=b.sid) sid_serial
 FROM v$lock a, v$lock b
 WHERE a.block = 1
   AND b.request > 0
   AND a.id1 = b.id1
   AND a.id2 = b.id2;


ALTER SYSTEM kill SESSION '&SID, &SERIAL' IMMEDIATE;

/*  ------------------------------------------------
	ALTER SYSTEM kill SESSION '1604,382' IMMEDIATE;
	ALTER SYSTEM kill SESSION '159,3422' IMMEDIATE;
	ALTER SYSTEM kill SESSION '159,3413' IMMEDIATE;
--------------------------------------------------- */


/* =========== LISTA SESSOES QUE estao inativas ===========*/

SELECT v$session.sid, v$session.serial#, v$session.user#,  v$session.username, v$session.osuser,
       v$session_wait.seconds_in_wait,v$session.status,  v$session_wait.state
FROM
   v$session, v$session_wait
WHERE
   v$session.sid = v$session_wait.sid AND
   v$session.status in ('INACTIVE', 'KILLED')

SET echo off
SET long 50000
SET pages 1000
SET lines 1000 trims on
col USR_BANC format a15;
col QUERY    format a50
col SO_PROC  format a15
COLUMN sid format 999999
COLUMN serial# format 9999999999
COLUMN logon format a15
SELECT 
	a.sid SID, 
	a.sql_id, 
	a.serial# SERIAL#,
	a.inst_id, 
	a.username USR_BANC,
	b.spid SO_PROC, 
	to_char(logon_time,'dd/mm/yy hh24:mi') logon, 
	c.sql_text QUERY 
FROM 
	gv$session a, gv$process b, gv$sqlarea c
WHERE 
	a.sql_address=c.address 
AND 
	a.paddr=b.addr 
AND 
	a.STATUS = 'ACTIVE';
--AND a.username not in ('SYSTEM','SYS') AND A.USERNAME IS NOT NULL;


/*====> VERIFICAR SESSÕES COM VALORES ALTO NA COLUNA LAST_CALL_ET E IDENTIFICAR A QUERIE <=====*/

SELECT sid,serial#, username, last_call_et 
FROM v$session WHERE status='ACTIVE' 
AND USERNAME IS NOT NULL
order by last_call_et desc;



SELECT sid,serial#, username, last_call_et 
FROM v$session WHERE USERNAME IS NOT NULL;


/* ---- VERIFICAR NA V$SESSION_WAIT ---- */

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A20
COLUMN event FORMAT A30

SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   v$session_wait sw,
       v$session s
WHERE  
	s.sid = sw.sid 
AND 
	sw.event not like 'SQL%' 
AND 
	sw.event not like 'rdbms%'
ORDER BY 
	sw.seconds_in_wait DESC; 
	
	
SELECT 
	sql_id,plan_hash_value,
	SUM(elapsed_time_total)/SUM(executions_total)/1000000 avg_elapsedt_secs
FROM 
	dba_hist_sqlstat
WHERE 
	sql_id = '7jmmw9f9749q6'
GROUP BY 
	sql_id,plan_hash_value ;

SET linesize 180
SET pagesize 100 
SELECT * FROM table( DBMS_XPLAN.display_cursor('7jmmw9f9749q6',NULL, 'ALL'));
 
/* ----> I/O de cada processo <---- */

SELECT 
	A.SID, 
	A.USERNAME, 
	A.OSUSER, 
	D.SPID, 
	C.NAME, 
	B.VALUE 
FROM 
	V$SESSION A, 
	V$SESSTAT B, 
	V$STATNAME C, 
	V$PROCESS D 
WHERE 
	A.SID = B.SID 
AND 
	A.PADDR = D.ADDR 
AND 
	B.STATISTIC# = C.STATISTIC# 
AND 
	C.NAME LIKE 'PHYSICAL%' 
AND 
	VALUE > 1000 
AND  
	A.SID=&SID
ORDER BY 
	B.VALUE;