Connected Sessions:Details

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
column COMM_KILL for a60
column INS format 99
column MACHINE for a8
column USERNAME for a8
column STATUS for a8
column SID heading 'SID' for a6
column SERIAL# heading 'SERIAL#' for a7
column SQL_ID for a15
column OS_PID for a6
column RUN_SECS for a7
column SQL_TEXT for a16
column PLAN_HASH_VALUE for a10 for 9999999999999
column LOGON_TIME for a9
column OSUSER for a6
column PROGRAM for a10
column MODULE for a10
column TERMINAL for a10
column MACHINE for a10
column EVENT for a6
col PGA_MB for a8 for 99999999
col CPU_SECS for a5 for 99999
col UNDO_PCT_READ for a4 for 99999
col TEMP_MB for a8 for 99999999
column REDO_MB for a6 for 999999
column BLOCK_CHANGES for a8 for 99999999
set lines 250
set pages 100
--first line sql text for current sessions in RAC
--SELECT 'ALTER SYSTEM KILL SESSION '''||t2.SID || ',' || t2.SERIAL# || ',@ '||t2.INST_ID || ''' immediate;' COMM_KILL
--'ALTER SYSTEM KILL SESSION '''||t1.SID || ',' || t1.SERIAL# || ''' immediate;' COMM_KILL
--,
SELECT * FROM ( SELECT
SUBSTR(T3.SQL_TEXT,1,100) SQL_TEXT,T1.INST_ID INS,T1.MACHINE,T1.USERNAME
,T1.STATUS,TO_CHAR(T1.SID) SID,TO_CHAR(T1.SERIAL#) SERIAL#,T1.SQL_ID--,T3.PLAN_HASH_VALUE
,(SELECT TO_CHAR(P.SPID) OS_PID FROM GV$PROCESS P WHERE T1.PADDR=P.ADDR AND P.INST_ID = T1.INST_ID) OS_PID
--,PROCESS
,TO_CHAR(T1.LOGON_TIME,'YYYYMMDD HH24:MI:SS') LOGON_TIME
,TO_CHAR(DECODE(T1.STATUS,'ACTIVE',T1.LAST_CALL_ET,0)) RUN_SECS
,(SELECT A.VALUE/(1024*1024) PGA_MB FROM GV$SESSTAT A,GV$STATNAME B WHERE A.STATISTIC#=B.STATISTIC#
AND UPPER(B.NAME) LIKE 'SESSION PGA MEMORY' AND A.INST_ID = B.INST_ID AND B.INST_ID = T1.INST_ID AND A.SID=T1.SID ) PGA_MB
,(SELECT SUM(ROUND (value/100)) FROM gv$sesstat ss, gv$statname st WHERE name LIKE '%CPU used by this session%'
AND ss.statistic# = st.statistic# AND ss.INST_ID = st.INST_ID AND ss.sid = T1.SID and ss.INST_ID = T1.INST_ID) CPU_SECS
,(SELECT CEIL(DECODE(CONSISTENT_CHANGES,0,0,CONSISTENT_CHANGES*100/CONSISTENT_GETS)) FROM GV$SESS_IO SIO WHERE SIO.INST_ID = T1.INST_ID AND SIO.SID=T1.SID) UNDO_PC_RD
,NVL((select TEMP_MB FROM (SELECT SID,INST_ID, SUM(MB_USED) TEMP_MB FROM (
SELECT S.sid, SUM (su.blocks) * TBS.block_size/1024/1024 mb_used,TBS.tablespace_name,S.INST_ID
FROM gv$sort_usage su, gv$session S, dba_tablespaces TBS
WHERE su.session_addr = S.saddr AND su.tablespace = TBS.tablespace_name and su.INST_ID = S.INST_ID
GROUP BY S.sid,TBS.block_size,TBS.tablespace_name,S.INST_ID ) GROUP BY SID,INST_ID ) A WHERE A.SID = T1.SID AND A.INST_ID = T1.INST_ID),0) TEMP_MB
,(SELECT CEIL(VALUE/1024/1024) FROM GV$STATNAME N, GV$SESSTAT S
WHERE UPPER(N.NAME) = 'REDO SIZE' AND S.STATISTIC# = N.STATISTIC# AND S.INST_ID = T1.INST_ID AND S.SID = T1.SID) REDO_MB
--,(SELECT (CEIL(I.BLOCK_CHANGES)) FROM GV$SESS_IO I WHERE I.INST_ID = T1.INST_ID AND I.SID = T1.SID) BLOCK_CHANGES
,T1.OSUSER,T1.EVENT--,SQL_CHILD_NUMBER
--,T1.PROGRAM--,T1.MODULE--,T1.TERMINAL
FROM
GV$SESSION T1
,GV$SQL T3
WHERE
T1.SID != SYS_CONTEXT('USERENV','SID') AND T1.INST_ID = T3.INST_ID(+) AND T1.USERNAME IS NOT NULL AND T1.SQL_ID = T3.SQL_ID(+)
AND T1.SQL_CHILD_NUMBER = T3.CHILD_NUMBER(+)
--AND T1.SQL_ID = any( '5tztp1g1p2s6b') --AND T1.PROGRAM = 'JDBC THIN CLIENT'--AND T1.MACHINE = 'SUNAMERICA-LA\WHPWNORA2'
--AND T1.LAST_CALL_ET > 0
--AND T1.STATUS = 'ACTIVE'
--AND T1.SID = ANY(678)
--and T1.USERNAME is not null
--AND UPPER(T1.USERNAME) = ('DEPOTHIST01')
--AND UPPER(MACHINE) LIKE '%LONINDAPNP18%'--AND UPPER(T1.OSUSER) LIKE '%DIBYAJOTI%'
--AND T1.PROGRAM LIKE '%SQLPLUS%'--AND UPPER(T3.SQL_TEXT) LIKE '%CREATE%'--AND T3.SQL_TEXT IS NOT NULL
)