-- ========================================================== 
-- 		Oracle query command to CHECK the SID (or instance name):
-- ========================================================== 
SELECT   sys_context('userenv', 'instance_name') FROM  DUAL;

-- ========================================================== 
-- 		Oracle query command TO CHECK database name (OR server host): 
-- ========================================================== 
SELECT  sys_context('userenv', 'server_host') FROM  DUAL;

-- ========================================================== 
-- 		Verifica SPID a partir do SID
-- ========================================================== 
SET echo off
SET long 50000
SET pages 1000
SET lines 150
COL username format a15
COL osuser format a20
COL cmd format a200

SELECT 
	P.SPID, S.SID, S.SERIAL#
FROM 
	gV$PROCESS P, gV$SESSION S
WHERE 
	P.ADDR = S.PADDR
AND 
	S.SID =1604;

 
SELECT     
	s.sid || ',' || s.serial# "SID/SERIAL",s.username,s.osuser,p.spid "OS PID",s.program
FROM     
	v$session s,v$process p
WHERE     
	s.paddr = p.addr
order by 
	to_number(p.spid)

-- ========================================================== 
--		Verifica sql com o SID
-- ========================================================== 
SET linesize 160
SET pagesize 66
COL c1 for a15
COL c1 heading "OS User"
COL c2 for a16
COL c2 heading "Oracle User"
COL b1 for a10
COL b1 heading "Unix PID"
COL b2 for 9999 justify left
COL b2 heading "ORA SID"
COL b3 for 999999 justify left
COL b3 heading "SERIAL#"
COL sql_text for a65
COL logon_time format a20
SET space 1
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3

  SELECT 
	c.spid b1, 
	b.osuser c1, 
	b.username c2, 
	b.sid b2,
	b.serial# b3, 
	a.sql_text,
	b.status
  --,to_char(b.logon_time, 'DD-MON-YYYY HH24:MI:SS') logon_time
    FROM 
		v$sqltext a, v$session b, v$process c
    WHERE 
		a.address = b.sql_address
--   AND b.status     = 'ACTIVE'
     AND b.paddr  = c.addr
     AND a.hash_value = b.sql_hash_value
     AND b.username IS NOT NULL
 AND b.sid=1730
ORDER BY c.spid,a.hash_value,a.piece

-- ========================================================== 
--		Verifica SQL com o PID
-- ========================================================== 
SET linesize 400
SET pagesize 66
COL c1 for a15
COL c1 heading "OS User"
COL c2 for a16
COL c2 heading "Oracle User"
COL b1 for a10
COL b1 heading "Unix PID"
COL b2 for 9999 justify left
COL b2 heading "ORA SID"
COL b3 for 999999 justify left
COL b3 heading "SERIAL#"
COL sql_text for a65
COL logon_time format a20
SET space 1
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3

  SELECT 
	c.spid b1, 
	b.osuser c1, 
	b.username c2, 
	b.sid b2,
	b.MACHINE,
	b.serial# b3, 
	a.sql_text,
	b.status,
	b.logon_time,
    to_char(b.logon_time, 'DD-MON-YYYY HH24:MI:SS') logon_time
  FROM 
	v$sqltext a, v$session b, v$process c
  WHERE a.address    = b.sql_address
     AND b.status     = 'ACTIVE'
     AND b.paddr      = c.addr
     AND a.hash_value = b.sql_hash_value
-- c.spid=29553
     AND b.username is not null
  ORDER BY c.spid,a.hash_value,a.piece;
  
-- ========================================================== 
--		Verifica informacoes usuarios com o SPID  
-- ==========================================================  
COL "SID/SERIAL" format a10
COL username format a15
COL osuser format a15
COL program format a40

SELECT
  s.sid || ',' || s.serial # "SID / SERIAL",
  s.username,
  s.osuser,
  p.spid "OS PID",
  s.program 
FROM
  v$session s,
  v$process p 
WHERE
  s.paddr = p.addr 
ORDER BY
  to_number(p.spid);
  
-- ========================================================== 
--		Mostra todos os Usuario Conectados
-- ========================================================== 
SELECT
  username,
  sid || ',' || serial # "ID",
  status,
  last_call_et "Last Activity" 
FROM
  v$session 
WHERE
  username IS NOT NULL 
ORDER BY
  status DESC,
  last_call_et DESC;

-- ========================================================== 
--		Verifica um determinado processo &=???
-- ========================================================== 
SELECT
  a.sid SID,
  a.serial # SERIAL #,
  a.USERNAME,
  osuser,
  a.sql_id,
  a.machine,
  a.terminal,
  a.program,
  sql_text cmd 
FROM
  v$session a,
  v$process b,
  v$sqlarea c 
WHERE
  a.sql_address = c.address 
  AND a.paddr = b.addr 
  AND b.spid =&spid 
ORDER BY  3,4;

-- ========================================================== 
--		Verifica um determinado processo(exemplo 123456) 
-- ========================================================== 
SELECT
  a.sid SID,
  a.serial # SERIAL #,
  a.USERNAME,
  osuser,
  a.sql_id,
  a.machine,
  a.terminal,
  a.program,
  a.status,
  a.MODULE,
  a.action 
FROM
  v$session a,
  v$process b,
  v$sqlarea c 
WHERE
  a.sql_address = c.address 
  AND a.paddr = b.addr 
  AND b.spid = 123456 
ORDER BY  3,4;


SELECT
  a.sid SID,
  a.serial # SERIAL #,
  a.USERNAME,
  osuser,
  a.sql_id,
  a.machine,
  a.terminal,
  a.program,
  a.status 
FROM
  v$session a,
  v$process b,
  v$sqlarea c 
WHERE
  a.sql_address = c.address 
  AND a.paddr = b.addr 	--AND a.sql_address = 'cxnww1xc46jh7' 
  AND a.sid = 490 
ORDER BY 3,4;
  

SELECT
  a.sid SID,
  a.status,
  sql_text 
FROM
  v $ session a,
  v $ process b,
  v $ sqlarea c 
WHERE
  a.sql_address = c.address 
  AND a.paddr = b.addr;
AND a.sid = 4271;
 

SELECT
  a.sid SID,
  a.status,
  sql_text 
FROM
  v$session a,
  v$process b,
  v$sqlarea c 
WHERE
  a.sql_address = c.address 
  AND a.paddr = b.addr 
  AND a.sid = 123;

-- ========================================================== 
--		Verifica todos os Processos
-- ==========================================================
SET line 600
SELECT
  a.sid SID,
  a.serial # SERIAL #,
  a.USERNAME,
  osuser,
  sql_text cmd 
FROM
  gv$session a,
  gv$process b,
  gv$sqlarea c 
WHERE
  a.sql_address = c.address 
  AND a.paddr = b.addr;
  
-- ==========================================================  
--		Verifica todos os Processos de Rollback
-- ==========================================================
COL machine for a10
COL logon for a20
COL username for a10
COL osuser for a10
COL "RB Segment Name" for a15
COL program for a42

SELECT
  r.NAME "RB Segment Name",
  dba_seg.size_mb,
  DECODE(TRUNC(SYSDATE - LOGON_TIME), 0, NULL, TRUNC(SYSDATE - LOGON_TIME) || ' Days' || ' + ') 
		|| TO_CHAR(TO_DATE(TRUNC(MOD(SYSDATE - LOGON_TIME, 1) * 86400), 'SSSSS'), 'HH24:MI:SS') LOGON,
  v$session.SID,
  v$session.SERIAL #,
  v$session.process,
  v$session.USERNAME,
  v$session.STATUS,
  v$session.OSUSER,
  v$session.MACHinE,
  v$session.PROGRAM 
FROM
  v$lock l,
  v$process p,
  v$rollname r,
  v$session,
  (
    SELECT
      segment_name,
      ROUND(bytes / (1024*1024), 2) size_mb 
    FROM
      dba_segments 
    WHERE
      segment_type = 'TYPE2 UNDO' 
    ORDER BY
      bytes DESC
  )
  dba_seg 
WHERE
  l.SID = p.pid( + ) 
  AND v$session.SID = l.SID 
  AND TRUNC (l.id1( + ) / 65536) = r.usn 
  AND l.TYPE( + ) = 'TX' 
  AND l.lmode( + ) = 6 
  AND r.NAME = dba_seg.segment_name 	--AND v$session.username = 'SYSTEM'
  --AND status = 'inACTIVE'
ORDER BY
  size_mb DESC;

-- ==========================================================
--		Executa o Explain
-- ==========================================================
SET pages 1200
SET lin 300
COL sql_text form a100
break on sql_id skip 1 on report
-- executa o explain informando o sql_id
DELETE FROM   plan_table;
COMMIT;
SELECT  * 
FROM  TABLE(dbms_xplan.display_cursor('cxnww1xc46jh7'));