Sunday, 8 March 2015

Oracle Session info

Session Info ...

http://anuj-singh.blogspot.com/2016/09/oracle-session-count-info-and-kill-and.html

http://anuj-singh.blogspot.com/2021/05/sql-history.html

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 200 pagesize 300
col "Kill--" for a150
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' /* '||action||' '||sql_id||' '||event||' started on '||logon_time||'*/;' "Kill--" from gv$session 
where 1=1
-- module='DBMS_SCHEDULER' 
and USERNAME='SYS'
and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'
;

set linesize 300 pagesize 300
col sql_text for a50 wrap
col kill for a17 
col event for a25
col osuser for a9
select /*+ gather_plan_statistics */ 
 ''''||sess.sid ||','|| sess.serial#||',@'||sess.inst_id ||'''' kill 
--serial#, sid
,osuser,  executions, sql.sql_id ,sql.child_number, event,p1,p2,sql_text
from gv$session sess join gv$sql sql 
on  (sess.sql_address = sql.address and sess.inst_id=sql.inst_id ) 
where sess.status = 'ACTIVE'
and sess.sid != (select distinct sid from sys.v_$mystat)

/set linesize 300 pagesize 1000
 col PLAN_TABLE_OUTPUT for a100
SELECT t.* FROM gv$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')) t WHERE s.sql_id in (select sql_id from gv$session where sql_id is not null 
--and USERNAME='USER' 
and sql_id='XXXXXX'
 )  ;

WITH x

AS (SELECT *

FROM dba_hist_active_sess_history

JOIN

dba_hist_snapshot

USING (snap_id, dbid, instance_number)

WHERE 1=1

begin_interval_time BETWEEN TO_DATE(

'28-MAY-2024 21:30'

,'DD-MON-YYYY HH24:MI'

)

AND TO_DATE(

'28-MAY-2024 23:59'

,'DD-MON-YYYY HH24:MI'

)

and begin_interval_time > sysdate -1/24

)

SELECT TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value ,session_id, event, COUNT( * )

FROM x

GROUP BY TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value ,session_id, event;

set linesize 300

col MODULE for a14

col ACTION for a24

col EVENT for a24

WITH y AS (

SELECT * FROM dba_hist_active_sess_history

WHERE 1=1

-- snap_id IN (xxxx, xxx)

--and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'

--and action like 'ORA$AT^_%' escape '^'

--and sample_time >sysdate -30

and sample_time > sysdate - interval '15' minute

and MODULE not in ('GoldenGate','DBMS_SCHEDULER','APEX Listener','KTSJ')

)

SELECT TRUNC(sample_time) sample_time, module, action, sql_id, sql_plan_hash_value ,session_id, event, COUNT( * )

FROM y

GROUP BY TRUNC(sample_time), module, action, sql_id, sql_plan_hash_value ,session_id, event;

SAMPLE_TI CON_ID MODULE ACTION SQL_ID SQL_PLAN_HASH_VALUE SESSION_ID EVENT COUNT(*)

--------- ---------- ------------------------ ------------------------ ------------- ------------------- ---------- ------------------------------ ----------

09-NOV-25 5 backup incr datafile 0000335 STARTED16 0 503 Backup: MML write backup piece 180

09-NOV-25 5 sqlplus@1331740-3pors03. 2u7vcdc1s2pn6 73398623 891 db file scattered read 23

corp.metrobank.plc.uk (T

09-NOV-25 5 dbfs_client@1331740-3por 0 2364 SQL*Net vector data to client 3

s03.corp.metrobank.plc.u


set linesize 300 pagesize 300
col MODULE for a24
col ACTION for a24
col EVENT for a30
WITH y   AS (
SELECT   *  FROM   gV$ACTIVE_SESSION_HISTORY
            WHERE 1=1
-- snap_id IN (xxxx, xxx)
--and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^'
--and action like 'ORA$AT^_%' escape '^'
--and sample_time >sysdate -30
and sample_time >  sysdate - interval '3' minute
and MODULE not in ('GoldenGate','DBMS_SCHEDULER','APEX Listener','KTSJ')
and event is not null                                    
 )
  SELECT   TRUNC(sample_time) sample_time,con_id, module, action, sql_id, sql_plan_hash_value ,session_id, event, COUNT( * )
    FROM   y
GROUP BY   TRUNC(sample_time),con_id, module, action, sql_id, sql_plan_hash_value ,session_id, event
 having COUNT( * ) >2
;



col inst_sid heading "INST_ID|:SID" format a7

col username format a10
col machine format a12
col sql_exec_start heading "SQL|START|D HH:MM:SS" format a11
col sql_id format a13
col sql_text format a40
col event format a33
col wait_sec heading "WAIT|(SEC)" format 99999
set linesize 200  pagesize 200
 col kill for a17
select
''''||ses.sid ||','|| ses.serial#||',@'||ses.inst_id ||'''' kill ,
-- ses.inst_id || chr(58) || ses.sid as inst_sid,
       username,
       (sysdate - sql_exec_start) day(1) to second(0) as sql_exec_start,
       ses.sql_id,
       substr(sql.sql_text, 1, 40) sql_text,
       substr(case time_since_last_wait_micro
                when 0 then (case wait_class when 'Idle' then 'IDLE: ' || event else event end)
                else 'ON CPU'
              end, 1, 33) event,
       (case time_since_last_wait_micro
          when 0 then wait_time_micro
          else time_since_last_wait_micro
        end) / 1000000 wait_secD
  from gv$session ses,
       gv$sqlstats sql
 where ses.inst_id || chr(58) || ses.sid <> sys_context('USERENV', 'INSTANCE') || chr(58) || sys_context('USERENV', 'SID')
   and username is not null
   and status = 'ACTIVE'
   and ses.sql_id = sql.sql_id(+)
 order by sql_exec_start,
          username,
          ses.sid,
          ses.sql_id;

set linesize 300 pagesize 1000

col PLAN_TABLE_OUTPUT for a100

SELECT t.* FROM gv$sql s, table(dbms_xplan.display_cursor(s.sql_id,s.child_number,FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE')) t WHERE s.sql_id in (select sql_id from gv$session where sql_id is not null

--and USERNAME='USER'

and sql_id='XXXXXX'

) ;

-- session.sql

DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
SET TERMOUT ON VERIFY OFF HEADING OFF LINESIZE 155 SQLBL ON ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK ON
--
-- Show information about the SESSION
SELECT distinct
        '    alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;'          || &_br ||
        '    INST_ID# ......... ' || RPAD(s.INST_ID           , 50, ' ') ||   'SID   ............ ' || s.sid               || &_br ||
        '    SERIAL# .......... ' || RPAD(s.serial#           , 50, ' ') ||   'USERNAME ......... ' || s.username          || &_br ||
        '    SPID  ............ ' || RPAD(p.spid              , 50, ' ') ||   'OPID ............. ' || p.pid               || &_br ||
        '    PADDR  ........... ' || RPAD(s.paddr             , 50, ' ') ||   'SADDR  ........... ' || s.saddr             || &_br ||
        '    AUDSID  .......... ' || RPAD(s.audsid            , 50, ' ') ||   'OSUSER  .......... ' || s.osuser            || &_br ||
        '    PROCESS  ......... ' || RPAD(s.process           , 50, ' ') ||   'PROGRAM  ......... ' || s.program           || &_br ||
        '    MACHINE  ......... ' || RPAD(s.machine           , 50, ' ') ||   'MODULE  .......... ' || s.module            || &_br ||
        '    HASH_VALUE. ...... ' || RPAD(s.sql_hash_value    , 50, ' ') ||   'PREV_HASH_VALUE .. ' || s.prev_hash_value   || &_br ||
        '    SQL_ID ........... ' || rpad(s.sql_id            , 50, ' ') ||   'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    PREV_SQL_ID ...... ' || rpad(s.prev_sql_id       , 50, ' ') ||   'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
        '    WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS        , 50, ' ') ||   'WAIT_TIME   ...... ' || w.WAIT_TIME         || &_br ||
        '    STATE      ........' || rpad(s.STATE             , 50, ' ') ||   'SERVICE NAME ......' || s.SERVICE_NAME      || &_br ||
        '    LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI')        , 50, ' ')||  'PROCESS..........'||s.PROCESS|| &_br ||
        '    P1.................' ||rpad(w.p1       , 50, ' ') ||  'p1text..........'||w.p1text||&_br ||
        '    P2.................' ||rpad(w.p2       , 50, ' ') ||  'p2text..........'||w.p2text||&_br ||
        '    P3.................' ||rpad(w.p3       , 50, ' ') ||  'p3text..........'||w.p3text||&_br ||
        '    EVENT#..............'||rpad(s.EVENT#   , 50, ' ') ||  'EVENT.......... '||s.EVENT||&_br  ||
        '    Status..............'||rpad(s.status   , 50, ' ') ||&_br ||  
        '    Sql_text............'||substr(sql_text,1,80)||&_br||
        '    p1 raw..............'||rpad(s.P1RAW        , 50, ' ')||' ***last call et.......... '||s.last_call_et|| &_br ||
        --|| 'Service Name........'||s.SERVICE_NAME
        '    EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
        '    EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
        '    alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'
FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
WHERE 1=1
-- and s.sid IN (1)
and s.paddr = p.addr
AND s.sid   = w.sid
and s.sql_id is not null
-- and s.sid =714
and s.INST_ID=p.INST_ID
and w.INST_ID=s.INST_ID
and sq.INST_ID=s.INST_ID
and sq.sql_id=s.sql_id
-- and s.USERNAME='CA'
-- and s.SERVICE_NAME not in  ('SYS$USERS','SYS$BACKGROUND')
--and s.SERVICE_NAME in ('CAV')
and s.sid >9 ;

==================================================================================

DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
set termout on verify off heading off linesize 155 sqlbl on echo off trimspool on trimout on feedback on
--
-- Show information about the SESSION
SELECT distinct
        '    alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;'          || &_br ||
        '    INST_ID# ......... ' || RPAD(s.INST_ID           , 50, ' ') ||   'SID   ............ ' || s.sid               || &_br ||
        '    SERIAL# .......... ' || RPAD(s.serial#           , 50, ' ') ||   'USERNAME ......... ' || s.username          || &_br ||
        '    SPID  ............ ' || RPAD(p.spid              , 50, ' ') ||   'OPID ............. ' || p.pid               || &_br ||
        '    PADDR  ........... ' || RPAD(s.paddr             , 50, ' ') ||   'SADDR  ........... ' || s.saddr             || &_br ||
        '    AUDSID  .......... ' || RPAD(s.audsid            , 50, ' ') ||   'OSUSER  .......... ' || s.osuser            || &_br ||
        '    PROCESS  ......... ' || RPAD(s.process           , 50, ' ') ||   'PROGRAM  ......... ' || s.program           || &_br ||
        '    MACHINE  ......... ' || RPAD(s.machine           , 50, ' ') ||   'MODULE  .......... ' || s.module            || &_br ||
        '    HASH_VALUE. ...... ' || RPAD(s.sql_hash_value    , 50, ' ') ||   'PREV_HASH_VALUE .. ' || s.prev_hash_value   || &_br ||
        '    SQL_ID ........... ' || rpad(s.sql_id            , 50, ' ') ||   'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    PREV_SQL_ID ...... ' || rpad(s.prev_sql_id       , 50, ' ') ||   'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
        '    SQL EXEC ID........'||  rpad(s.SQL_EXEC_ID       , 40, ' ') ||   'Tracefile  ........' || p.TRACEFILE         || &_br ||   ----- comment 10g 
  '    WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS        , 50, ' ') ||   'WAIT_TIME   ...... ' || w.WAIT_TIME         || &_br ||
        '    STATE      ........' || rpad(s.STATE             , 50, ' ') ||   'SERVICE NAME ......' || s.SERVICE_NAME      || &_br ||
        '    LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI')        , 50, ' ')||'PROCESS..........'||s.PROCESS|| &_br ||
        '    P1.................' ||rpad(w.p1       , 50, ' ') ||  'p1text..........'||w.p1text||&_br ||
        '    P2.................' ||rpad(w.p2       , 50, ' ') ||  'p2text..........'||w.p2text||&_br ||
        '    P3.................' ||rpad(w.p3       , 50, ' ') ||  'p3text..........'||w.p3text||&_br ||
        '    EVENT#..............'||rpad(s.EVENT#   , 50, ' ') ||  'EVENT.......... '||s.EVENT ||&_br  ||
        '    Status..............'||rpad(s.status   , 50, ' ') ||&_br ||  
        '    Sql_text............'||substr(sql_text,1,80)||&_br||
        '    p1 raw..............'||rpad(s.P1RAW        , 50, ' ')||' ***last call et.......... '||to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '99999.99')|| &_br ||
    --|| 'Service Name........'||s.SERVICE_NAME
 -- 'SQL EXEC ID........'||rpad(s.SQL_EXEC_ID  , 50, ' ') ||'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
        '    EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
        '    alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'|| &_br ||
        '    select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' || &_br ||  
        '    select * from table(dbms_xplan.display_awr('||''''||s.sql_id ||''''||',null,null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' || &_br ||  
        '    alter system set events ''trace[RDBMS.SQL_Optimizer.*][sql:'||s.sql_id ||']'';'  || &_br ||   
        '    alter system set events ''trace[RDBMS.SQL_Optimizer.*][sql:'||s.sql_id ||'] off'';'  
  FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
where 1=1
and s.paddr    = p.addr
and s.inst_id  = p.inst_id
and w.inst_id  = s.inst_id
and sq.inst_id = s.inst_id
and sq.sql_id  = s.sql_id
and s.sid      = w.sid
-- and s.machine='S0017'
-- and s.sql_id is not null
-- nad s.sid!=sys_context('userenv','sid')
-- and sq.sql_id in ('1jwzgruw1z6s3' )
-- and s.EVENT not like 'SQL%' 
-- and s.username='SYS'
-- and s.service_name  in  ('USER')
-- and s.sid  in (23826)
-- and ROUND((SYSDATE - s.LOGON_TIME)*(24*60),1) > 60  ---- 1hr 
and s.type != 'BACKGROUND'
and s.username is not null
-- and s.status  ='INACTIVE'
 and s.USERNAME  Not  in  ( 'SYS ' ,  'SYSTEM' ,  'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
/

==========

define sid=1473

set lines 200 pagesize 300 echo off

col sess format a10

col stime format a08

col sta format a04 head 'Sta'

col event format a24 trunc

col seq# format 99999

col wtime format 9999999

col twaited format 99999999

col p1p2p3 format a18 head ' P1:P2:P3'

col wclass format a11 trunc

col sqlid_cn format a16 head 'SqlId:ChildNo'

col blk format a01 head 'X'

col oc format 999

col cn format 99

col flags format a12 head 'CPH S PRXJBZ'

col mod format a20 head 'Module' trunc

col sqlstart format a05 head 'SQL|Start'

col sqlxid format 99999999 head 'SqlExecId'

col sidser format a15 head 'Sid,Serial<Blk'

col dur format 999999 head 'Dur|ms'

col cpu format 9999 head 'CPU|ms'

col dbtime format 999999 head 'DB Time|ms'

break on sidser skip 1 on mod on sqlid_cn on sqlxid on sqlstart

select

''''||SESSION_ID ||','|| session_serial#||',@'||inst_id ||''''||decode(substr(blocking_session_status,1,1),'V','<',' ')||lpad(blocking_session,4,' ') sidser

,to_char(sample_time, 'HH24:MI:SS') stime

,module mod

,sql_id||decode(sql_child_number,-1,' ',':'||lpad(sql_child_number,2,' ')) sqlid_cn

,sql_exec_id sqlxid

,to_char(sql_exec_start,'MI:SS') sqlstart

,in_connection_mgmt||in_parse||in_hard_parse||' '||in_sql_execution||' '||in_plsql_execution||in_plsql_rpc||in_java_execution||in_bind||in_cursor_close flags

,decode(session_state,'WAITING','Wait','ON CPU','cpu',' ? ') sta

,seq# seq#

,event event

,lpad(least(p1,9999999),7,' ')||':'||lpad(least(p2,999999),6,' ')||':'||lpad(least(p3,9),3,' ') p1p2p3

,wait_class wclass

,tm_delta_time/1000 dur

,tm_delta_cpu_time/1000 cpu

,tm_delta_db_time/1000 dbtime

from gv$active_session_history

where 1=1

and session_id = '&sid'

--and sample_time between sysdate-70/1440 and sysdate-30/1440

and sample_time > sysdate -1

and sample_time > sysdate - interval '30' minute

order by sample_time

;

=========================

set linesize 2000 pagesize 500 
col LONGOPS_MESSAGE for a20 
col PREV_SQL_TEXT for a50 wrap
col SQL_TEXT for a50 wrap 
col  P1TEXT for a10                                                                    
col  P2TEXT  for a10                                                                 
col  P3TEXT for a10
col USERNAME for a10
col PROGRAM for a10 
col EVENT for a12
col HOST_NAME for a12
col LATCHNAME for a12
col MACHINE for a12
col kill for a15
select/*+ rule*/ distinct   --because of multiple records in v$sql may exist
to_char(gv$session.LOGON_TIME,'YYYY.MM.DD HH24:MI:SS') as LOGON_TIME
,''''||gv$session.sid ||','|| gv$session.serial#||',@'||gv$session.inst_id ||'''' kill
, gv$session.STATUS
, gv$session.USERNAME
, gv$session.PROGRAM
--, gv$session.INST_ID
--, gv$session.SID
--, gv$session.SERIAL#
, gv$transaction.START_TIME as TX_STIME
, gv$session.LAST_CALL_ET as LAST_CALL_ET
, gv$session_wait.EVENT
, v$latchname.NAME as LATCHNAME
, gv$session_wait.SECONDS_IN_WAIT
, dba_objects.owner||'.'||dba_objects.object_name req_object
, decode(nvl(gv$session.ROW_WAIT_OBJ#,-1),-1,'NONE',DBMS_ROWID.ROWID_CREATE( 1,
gv$session.ROW_WAIT_OBJ#, gv$session.ROW_WAIT_FILE#, gv$session.ROW_WAIT_BLOCK#,
gv$session.ROW_WAIT_ROW# )) req_rowid
, lockhold.inst_id as BLOCKING_INSTANCE
, lockhold.sid as BLOCKING_SESSION
, gv$session.COMMAND
, gv$session_longops.TIME_REMAINING as LONGOPS_CALL_RT
, gv$session_longops.MESSAGE as LONGOPS_MESSAGE
, gv$session_wait.P1
, gv$session_wait.P1TEXT
, gv$session_wait.P2 P2
, gv$session_wait.P2TEXT
, gv$session_wait.P3
, gv$session_wait.P3TEXT
, gv$sql.ADDRESS as SQL_ADDRESS
, gv$sql.HASH_VALUE as HASH_VALUE
, gv$sql.PLAN_HASH_VALUE as PLAN_HASH_VALUE
, gv$sql.OPTIMIZER_MODE as SQL_OPTIMIZER_MODE
, gv$sql.sql_text as SQL_TEXT
, sql1.ADDRESS as PREV_SQL_ADDRESS
, sql1.HASH_VALUE as PREV_HASH_VALUE
, sql1.PLAN_HASH_VALUE as PREV_PLAN_HASH_VALUE
, sql1.OPTIMIZER_MODE as PREV_SQL_OPTIMIZER_MODE
, sql1.sql_text as PREV_sql_text
--Network connection properties
, gv$session.SERVER
, gv$session.FAILOVER_TYPE
, gv$session.FAILOVER_METHOD
, gv$session.FAILED_OVER
, gv$session.FIXED_TABLE_SEQUENCE
--OS properties
, gv$session.MACHINE
, gv$session.MODULE
, gv$session.OSUSER
, gv$session.OWNERID
, gv$session.TERMINAL
-- , 'Alter system kill session '''||gv$session.SID||','||gv$session.SERIAL#||''';' as KILL_SESSION
,'alter system kill session ' || '''' || gv$session.sid || ',' || gv$session.serial# ||',@'|| gv$session.inst_id || '''' || ' immediate;' as KILL_SESSION
, gv$instance.HOST_NAME
, 'kill '||gv$process.SPID as KILL_SPID
from gv$session
, gv$instance
, gv$process
, gv$session_wait
, gv$sql
, gv$sql sql1
, gv$transaction
, v$latchname
, dba_objects
, gv$session_longops
, gv$lock lockwait
, gv$lock lockhold
where gv$session.INST_ID = gv$instance.INST_ID
and gv$session.PADDR=gv$process.ADDR(+)
and gv$session.INST_ID = gv$process.INST_ID(+)
and gv$session.sql_address=gv$sql.address(+)
and gv$session.INST_ID = gv$sql.INST_ID(+)
and gv$session.sql_hash_value=gv$sql.hash_value(+)
and gv$session.PREV_SQL_ADDR = sql1.address(+)
and gv$session.PREV_HASH_VALUE = sql1.hash_value(+)
and gv$session.SID=gv$session_wait.SID(+)
and gv$session.INST_ID = gv$session_wait.INST_ID(+)
and gv$session.SADDR=gv$transaction.SES_ADDR(+)
and gv$session.INST_ID = gv$transaction.INST_ID(+)
and (gv$session.SERIAL# <> 1 or upper(gv$session.PROGRAM) like '%LGWR%'
or upper(gv$session.PROGRAM) like '%DBW%' )--System session excluded,exc. DBWR
and upper(gv$session.PROGRAM) not like '%QMNC%'--Queue Monitor Coordinator excluded
and upper(gv$session.PROGRAM) not like '%Q0%'--Queue monitor processes excluded
and gv$session_wait.P2=v$latchname.LATCH#(+)
and gv$session.ROW_WAIT_OBJ# = dba_objects.object_id(+)
and gv$session.SID = gv$session_longops.sid(+)
and gv$session.INST_ID = gv$session_longops.INST_ID(+)
and gv$session.SERIAL# = gv$session_longops.SERIAL#(+)
and gv$session.sql_address = gv$session_longops.SQL_ADDRESS(+)
and gv$session.sql_hash_value = gv$session_longops.SQL_HASH_VALUE(+)
and nvl(gv$session_longops.SOFAR,0) <> nvl(gv$session_longops.TOTALWORK,1)
and gv$session.LOCKWAIT = lockwait.KADDR(+)
and lockwait.id1 = lockhold.id1(+)
and lockwait.id2 = lockhold.id2(+)
and nvl(lockwait.REQUEST,1) > 0
and nvl(lockwait.LMODE,0) = 0
and nvl(lockhold.REQUEST,0) = 0
and nvl(lockhold.LMODE,1) > 0
and nvl(lockwait.SID,0) <> nvl(lockhold.SID,1)
--and gv$session.schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL','SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')                     
order by tx_stime, status, LOGON_TIME, username, machine asc;

 =============

Session Info ....



SET    PAGES   0       -
       NEWP    0       -
       LINES   150     -
       TERM    Off     -
       HEAD    Off

Col     X form a150
--spool  all_session.log
TTitle  T Skip 2

select  ' ' x
, lpad( 'SADDR', 24, ' ')			||' : '||s.saddr x
, lpad( '_', 43, '_') x
, lpad( 'SID', 24, ' ')				||' : '||s.sid x
, lpad( 'Sid_serial', 24, ' ')			||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x
, lpad( 'SERIAL#', 24, ' ')			||' : '||s.serial# x
, lpad( 'AUDSID', 24, ' ')			||' : '||s.audsid x
, lpad( 'PADDR', 24, ' ')			||' : '||s.paddr x
, lpad( 'USER#', 24, ' ')			||' : '||s.user# x
, lpad( 'USERNAME', 24, ' ')			||' : '||s.username x
, lpad( 'COMMAND', 24, ' ')			||' : '||s.command x
, lpad( 'TADDR', 24, ' ')			||' : '||s.taddr x
, lpad( 'LOCKWAIT', 24, ' ')			||' : '||s.lockwait x
, lpad( 'STATUS', 24, ' ')			||' : '||s.status x
, lpad( 'SERVER', 24, ' ')			||' : '||s.server x
, lpad( 'SCHEMA#', 24, ' ')			||' : '||s.schema# x
, lpad( 'SCHEMANAME', 24, ' ')			||' : '||s.schemaname x
, lpad( 'OSUSER', 24, ' ')			||' : '||s.osuser x
, lpad( 'Client Process', 24, ' ')		||' : '||s.process x
, lpad( 'Server Process', 24, ' ')		||' : '||p.spid x
, lpad( 'MACHINE', 24, ' ')			||' : '||s.machine x
, lpad( 'TERMINAL', 24, ' ')			||' : '||s.terminal x
, lpad( 'PROGRAM', 24, ' ')			||' : '||s.program x
, lpad( 'TYPE', 24, ' ')			||' : '||s.type x
, lpad( 'SQL_ID', 24, ' ')			||' : '||s.sql_id x
, lpad( 'PREV_SQL_ID', 24, ' ')			||' : '||s.prev_sql_id x
, lpad( 'SQL_ADDRESS', 24, ' ')			||' : '||s.sql_address x
, lpad( 'SQL_HASH_VALUE', 24, ' ')		||' : '||s.sql_hash_value x
, lpad( 'PREV_SQL_ADDR', 24, ' ')		||' : '||s.prev_sql_addr x
, lpad( 'PREV_HASH_VALUE', 24, ' ')		||' : '||s.prev_hash_value x
, lpad( 'MODULE', 24, ' ')			||' : '||s.module x
, lpad( 'MODULE_HASH', 24, ' ')			||' : '||s.module_hash x
, lpad( 'ACTION', 24, ' ')			||' : '||s.action x
, lpad( 'ACTION_HASH', 24, ' ')			||' : '||s.action_hash x
, lpad( 'CLIENT_INFO', 24, ' ')			||' : '||s.client_info x
, lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ')        ||' : '||s.fixed_table_sequence x
, lpad( 'ROW_WAIT_OBJ#', 24, ' ')		||' : '||s.row_wait_obj# x
, lpad( 'ROW_WAIT_FILE#', 24, ' ')		||' : '||s.row_wait_file# x
, lpad( 'ROW_WAIT_BLOCK#', 24, ' ')		||' : '||s.row_wait_block# x
, lpad( 'ROW_WAIT_ROW#', 24, ' ')		||' : '||s.row_wait_row# x
, lpad( 'LOGON_TIME', 24, ' ')			||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x
, lpad( 'LAST_CALL_ET', 24, ' ')		||' : '||s.last_call_et x
, lpad( 'SQL FIRST_LOAD_TIME', 24, ' ')	        ||' : '||sql.first_load_time x
, lpad( 'FINAL_BLOCKING* ..', 24, ' ')	        ||' : '||s.final_blocking_session_status x
, lpad( 'EVENT ..', 24, ' ')			||' : '||s.event x
, lpad( 'SQL', 24, ' ')				||' : '||sql.sql_text x
from    gv$sqlarea sql,gv$process p,gv$session s
where 1=1
and     p.addr             = s.paddr
and	    p.inst_id          = s.inst_id
and     nvl( s.sql_address, 'FFFFFFFF') = sql.Address
and     s.inst_id = sql.inst_id
And     Nvl( s.SERIAL#, 0) > 1
--and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
-- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
/


***********************************



--- with Con_id


DEFINE _br=chr(10)
DEFINE _trcsid_session_serial="NA"
COLUMN trcsid_session_serial NEW_VALUE _trcsid_session_serial
DEFINE _trcsid_session_serial="NA"
SET TERMOUT ON VERIFY OFF HEADING OFF LINESIZE 155 SQLBL ON ECHO OFF TRIMSPOOL ON TRIMOUT ON FEEDBACK ON
--
-- Show information about the SESSION
SELECT distinct
        '    alter system kill Session  ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||''''||' immediate;'          || &_br ||
        '    CON_ID..............'||rpad(s.CON_ID   , 50, ' ') ||&_br || 
        '    INST_ID# ......... ' || RPAD(s.INST_ID           , 50, ' ') ||   'SID   ............ ' || s.sid               || &_br ||
        '    SERIAL# .......... ' || RPAD(s.serial#           , 50, ' ') ||   'USERNAME ......... ' || s.username          || &_br ||
        '    SPID  ............ ' || RPAD(p.spid              , 50, ' ') ||   'OPID ............. ' || p.pid               || &_br ||
        '    PADDR  ........... ' || RPAD(s.paddr             , 50, ' ') ||   'SADDR  ........... ' || s.saddr             || &_br ||
        '    AUDSID  .......... ' || RPAD(s.audsid            , 50, ' ') ||   'OSUSER  .......... ' || s.osuser            || &_br ||
        '    PROCESS  ......... ' || RPAD(s.process           , 50, ' ') ||   'PROGRAM  ......... ' || s.program           || &_br ||
        '    MACHINE  ......... ' || RPAD(s.machine           , 50, ' ') ||   'MODULE  .......... ' || s.module            || &_br ||
        '    HASH_VALUE. ...... ' || RPAD(s.sql_hash_value    , 50, ' ') ||   'PREV_HASH_VALUE .. ' || s.prev_hash_value   || &_br ||
        '    SQL_ID ........... ' || rpad(s.sql_id            , 50, ' ') ||   'CHILD_NUMBER# .... ' || s.sql_child_number  || &_br ||
        '    PREV_SQL_ID ...... ' || rpad(s.prev_sql_id       , 50, ' ') ||   'PREV_CHILD# ...... ' || s.prev_child_number || &_br ||
        '    WAIT_CLASS ....... ' || rpad(w.WAIT_CLASS        , 50, ' ') ||   'WAIT_TIME   ...... ' || w.WAIT_TIME         || &_br ||
        '    STATE      ........' || rpad(s.STATE             , 50, ' ') ||   'SERVICE NAME ......' || s.SERVICE_NAME      || &_br ||
        '    LOGON_TIME ........' || rpad(TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI')        , 50, ' ')||  'PROCESS..........'||s.PROCESS|| &_br ||
        '    P1.................' ||rpad(w.p1       , 50, ' ') ||  'p1text..........'||w.p1text||&_br ||
        '    P2.................' ||rpad(w.p2       , 50, ' ') ||  'p2text..........'||w.p2text||&_br ||
        '    P3.................' ||rpad(w.p3       , 50, ' ') ||  'p3text..........'||w.p3text||&_br ||
        '    EVENT#..............'||rpad(s.EVENT#   , 50, ' ') ||  'EVENT.......... '||s.EVENT||&_br  ||
        '    Status..............'||rpad(s.status   , 50, ' ') ||&_br ||  
        '    Sql_text............'||substr(sql_text,1,80)||&_br||
        '    p1 raw..............'||rpad(s.P1RAW        , 50, ' ')||' ***last call et.......... '||s.last_call_et|| &_br ||
        --|| 'Service Name........'||s.SERVICE_NAME
        '    EXECUTE dbms_monitor.serv_mod_act_trace_enable (service_name=>'||''''||SERVICE_NAME||''''||',binds=>true,waits=>true);' || &_br ||
        '    EXECUTE DBMS_MONITOR.session_trace_enable(session_id =>'||s.sid||','|| 'serial_num=>'||s.serial#||','||' waits=>TRUE, binds=>TRUE);'|| &_br ||
        '    alter system set events '||''''|| 'sql_trace [sql:'||s.sql_id||'] level 12'||''''||';'
FROM gv$session s, gv$session_wait w, gv$process p,gv$sql sq
WHERE 1=1
-- and s.sid IN (1)
and s.paddr = p.addr
AND s.sid   = w.sid
and s.sql_id is not null
-- and s.sid =714
and s.INST_ID=p.INST_ID
and w.INST_ID=s.INST_ID
and sq.INST_ID=s.INST_ID
and sq.sql_id=s.sql_id
-- and s.USERNAME='CA'
-- and s.SERVICE_NAME not in  ('SYS$USERS','SYS$BACKGROUND')
--and s.SERVICE_NAME in ('CAV')
and s.CON_ID=3
and s.sid >9 ;




--- with Con_id




SET    PAGES   0       -
       NEWP    0       -
       LINES   150     -
       TERM    Off     -
       HEAD    Off

Col     X form a150
--spool  all_session.log
TTitle  T Skip 2

select  ' ' x
, lpad( 'SADDR', 24, ' ')			||' : '||s.saddr x
, lpad( '_', 43, '_') x
, lpad( 'SID', 24, ' ')				||' : '||s.sid x
, lpad( 'Sid_serial', 24, ' ')			||' : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' x
, lpad( 'Con _id', 24, ' ')			||' : '||s.Con_id
, lpad( 'SERIAL#', 24, ' ')			||' : '||s.serial# x
, lpad( 'AUDSID', 24, ' ')			||' : '||s.audsid x
, lpad( 'PADDR', 24, ' ')			||' : '||s.paddr x
, lpad( 'USER#', 24, ' ')			||' : '||s.user# x
, lpad( 'USERNAME', 24, ' ')			||' : '||s.username x
, lpad( 'COMMAND', 24, ' ')			||' : '||s.command x
, lpad( 'TADDR', 24, ' ')			||' : '||s.taddr x
, lpad( 'LOCKWAIT', 24, ' ')			||' : '||s.lockwait x
, lpad( 'STATUS', 24, ' ')			||' : '||s.status x
, lpad( 'SERVER', 24, ' ')			||' : '||s.server x
, lpad( 'SCHEMA#', 24, ' ')			||' : '||s.schema# x
, lpad( 'SCHEMANAME', 24, ' ')			||' : '||s.schemaname x
, lpad( 'OSUSER', 24, ' ')			||' : '||s.osuser x
, lpad( 'Client Process', 24, ' ')		||' : '||s.process x
, lpad( 'Server Process', 24, ' ')		||' : '||p.spid x
, lpad( 'MACHINE', 24, ' ')			||' : '||s.machine x
, lpad( 'TERMINAL', 24, ' ')			||' : '||s.terminal x
, lpad( 'PROGRAM', 24, ' ')			||' : '||s.program x
, lpad( 'TYPE', 24, ' ')			||' : '||s.type x
, lpad( 'SQL_ID', 24, ' ')			||' : '||s.sql_id x
, lpad( 'PREV_SQL_ID', 24, ' ')			||' : '||s.prev_sql_id x
, lpad( 'SQL_ADDRESS', 24, ' ')			||' : '||s.sql_address x
, lpad( 'SQL_HASH_VALUE', 24, ' ')		||' : '||s.sql_hash_value x
, lpad( 'PREV_SQL_ADDR', 24, ' ')		||' : '||s.prev_sql_addr x
, lpad( 'PREV_HASH_VALUE', 24, ' ')		||' : '||s.prev_hash_value x
, lpad( 'MODULE', 24, ' ')			||' : '||s.module x
, lpad( 'MODULE_HASH', 24, ' ')			||' : '||s.module_hash x
, lpad( 'ACTION', 24, ' ')			||' : '||s.action x
, lpad( 'ACTION_HASH', 24, ' ')			||' : '||s.action_hash x
, lpad( 'CLIENT_INFO', 24, ' ')			||' : '||s.client_info x
, lpad( 'FIXED_TABLE_SEQUENCE', 24, ' ')        ||' : '||s.fixed_table_sequence x
, lpad( 'ROW_WAIT_OBJ#', 24, ' ')		||' : '||s.row_wait_obj# x
, lpad( 'ROW_WAIT_FILE#', 24, ' ')		||' : '||s.row_wait_file# x
, lpad( 'ROW_WAIT_BLOCK#', 24, ' ')		||' : '||s.row_wait_block# x
, lpad( 'ROW_WAIT_ROW#', 24, ' ')		||' : '||s.row_wait_row# x
, lpad( 'LOGON_TIME', 24, ' ')			||' : '||to_char( s.logon_time, 'dd mon yyyy hh24:mi:ss') x
, lpad( 'LAST_CALL_ET', 24, ' ')		||' : '||s.last_call_et x
, lpad( 'SQL FIRST_LOAD_TIME', 24, ' ')	        ||' : '||sql.first_load_time x
, lpad( 'FINAL_BLOCKING* ..', 24, ' ')	        ||' : '||s.final_blocking_session_status x
, lpad( 'EVENT ..', 24, ' ')			||' : '||s.event x
, lpad( 'SQL', 24, ' ')				||' : '||sql.sql_text x
from    gv$sqlarea sql,gv$process p,gv$session s
where 1=1
and     p.addr             = s.paddr
and	    p.inst_id          = s.inst_id
and     nvl( s.sql_address, 'FFFFFFFF') = sql.Address
and     s.inst_id = sql.inst_id
And     Nvl( s.SERIAL#, 0) > 1
--and schemaname not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' ,'WK_TEST', 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','DP_MON','FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
-- 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC','SI_INFORMTN_SCHEMA','LBACSYS','DBVISIT7')
/

=====


	
  SELECT COUNT (*),
         CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END
            AS state,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
            AS sw_event
    FROM gv$session_wait
GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
ORDER BY 1 DESC, 2 DESC




  SELECT COUNT (*),
         CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END
            AS state,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
            AS sw_event
    FROM gv$session
   WHERE TYPE = 'USER' 
AND status = 'ACTIVE'
GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END,
         CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END
ORDER BY 1 DESC, 2 DESC	
	


define sid='2594'
set pagesize 300 linesize 300
col sw_event    head EVENT for a40 truncate
col sw_p1transl head P1TRANSL for a42
col sw_sid      head SID for 999999

col sw_p1       head P1 for a18 justify left word_wrap
col sw_p2       head P2 for a18 justify left word_wrap
col sw_p3       head P3 for a18 justify left word_wrap

select 
    sid sw_sid, 
    CASE WHEN state != 'WAITING' THEN 'WORKING'
         ELSE 'WAITING'
    END AS state, 
    CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
         ELSE event
    END AS sw_event, 
    seq#, 
    seconds_in_wait sec_in_wait, 
    CASE state WHEN 'WAITING' THEN NVL2(p1text,p1text||'= ',null)||CASE WHEN P1 < 536870912 THEN to_char(P1) ELSE '0x'||rawtohex(P1RAW) END ELSE null END SW_P1,
    CASE state WHEN 'WAITING' THEN NVL2(p2text,p2text||'= ',null)||CASE WHEN P2 < 536870912 THEN to_char(P2) ELSE '0x'||rawtohex(P2RAW) END ELSE null END SW_P2,
    CASE state WHEN 'WAITING' THEN NVL2(p3text,p3text||'= ',null)||CASE WHEN P3 < 536870912 THEN to_char(P3) ELSE '0x'||rawtohex(P3RAW) END ELSE null END SW_P3,
    CASE state WHEN 'WAITING' THEN 
        CASE 
            WHEN event like 'cursor:%' THEN
                '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))
                    WHEN event like 'enq%' AND state = 'WAITING' THEN 
                '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||
                chr(bitand(p1, -16777216)/16777215)||
                chr(bitand(p1,16711680)/65535)||
                ' mode '||bitand(p1, power(2,14)-1)
            WHEN event like 'latch%' AND state = 'WAITING' THEN 
                  '0x'||trim(to_char(p1, 'XXXXXXXXXXXXXXXX'))||': '||(
                        select name||'[par' 
                            from v$latch_parent 
                            where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
                        union all
                        select name||'[c'||child#||']' 
                            from v$latch_children 
                            where addr = hextoraw(trim(to_char(p1,rpad('0',length(rawtohex(addr)),'X'))))
                  )
            WHEN event like 'library cache pin' THEN
                  '0x'||RAWTOHEX(p1raw)
        ELSE NULL END 
    ELSE NULL END AS sw_p1transl
FROM 
    gv$session_wait 
WHERE 1=1
 and  sid IN ('&sid')
ORDER BY
    state,
    sw_event,
    p1,
    p2,
    p3
	

====



define sid=''

set time on timing on
set lines 500 pages 60
column sid 						format 9999
column state 					format a10 trunc
column username 				format a20 wrap
column osuser 					format a8
column seq 						for 99999
column et 						for 99999999999 heading 'Minu|Elaps'
column wait_time heading 'Sec.|Wtd' format 999
column wis heading 'Sec.|Wtng' format 9999999999999
col event 						for a25 trunc
col mod 						for a36 wrap
col  P1_P2_P3_TEXT 				for a40 wrap
col process 					for a8
col pid 						for a6 heading DBP
col sql_hash_value 				for 9999999999
col serial# heading 'Sr.|No.' 	for 99999
col inst_id     				for 99 heading 'IN|ST'
col kill1 						for a17
col kill for a17

--set feed on pause on term on
select
 ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
 ,s.con_id
-- , to_char(p.spid,'9999999′) SPID 
,'kill -9 '||to_char(p.spid) kill1
, s.sql_id
,decode(w.event,'latch free', w.event||'-p2='||to_char(w.p2), substr(w.event,1,28)) event
, s.username username
,substr(s.osuser, 1,10) osuser
,w.state,w.wait_time, w.seconds_in_wait wis, w.seq# seq, s.sql_hash_value, s.last_call_et/60 et,s.module ||'~'||s.action mod,s.status,
substr(w.p1text||' '||to_char(s.P1)||'-'||w.p2text||' '||to_char(s.P2)||'-'||   w.p3text||' '||to_char(s.P3), 1,40) P1_P2_P3_TEXT
from gv$session_wait w, gv$session s, gv$process p
where s.sid=w.sid
and p.addr  = s.paddr
--and s.status in ( 'ACTIVE', 'KILLED')
and s.username is not null
and w.inst_id = s.inst_id
and s.inst_id = p.inst_id
and s.username not in ('SYS','SYSTEM')
--and s.sid = &sid
--order by 14
/
                   





set termout off  verify off trimspool on linesize 300 longchunksize 200000 long 200000 pages 0
column txt format a200

COL dummy_value NOPRINT for a300
--
SELECT 'dummy_value' dummy_value,
  'USERNAME    : ' || s.username     || CHR(10) ||
  'SCHEMA      : ' || s.schemaname   || CHR(10) ||
  'OSUSER      : ' || s.osuser       || CHR(10) ||
  'MODULE      : ' || s.program      || CHR(10) ||
  'ACTION      : ' || s.schemaname   || CHR(10) ||
  'CLIENT INFO : ' || s.osuser       || CHR(10) ||
  'PROGRAM     : ' || s.program      || CHR(10) ||
  'kill -9     : ' || p.spid      || CHR(10) ||
  --'SPID        : ' || p.spid         || CHR(10) ||
   --'KILL STRING : ' || ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' || CHR(10) ||
'KILL STRING : ' || 'alter system kill session ' || '''' || s.sid || ',' || s.serial# ||',@'|| s.inst_id || '''' || ' immediate;'|| CHR(10) ||
  'Con Id      : ' || s.con_id || CHR(10) ||
  'MACHINE     : ' || s.machine      || CHR(10) ||
  'TYPE        : ' || s.type         || CHR(10) ||
  'TERMINAL    : ' || s.terminal     || CHR(10) ||
  'CPU         : ' || q.cpu_time/1000000     || CHR(10) ||
  'ELAPSED_TIME: ' || q.elapsed_time/1000000 || CHR(10) ||
  'BUFFER_GETS : ' || q.buffer_gets  || CHR(10) ||
  'SQL_ID      : ' || q.sql_id       || CHR(10) ||
  'CHILD_NUM   : ' || q.child_number || CHR(10) ||
  'START_TIME  : ' || TO_CHAR(s.sql_exec_start,'dd-mon-yy hh24:mi') || CHR(10) || 
  'STATUS      : ' || s.status       || CHR(10) ||
  'P1   	: ' || s.p1text || ' ' || s.p1  || chr(10)|| 
  'P2   	: ' || s.p2text || ' ' || s.p2  || chr(10)|| 
 'P3   		: ' || s.p3text || ' ' || s.p3  || chr(10)|| 
'FINAL_BLOCKING_SESSION   		: '                ||s.FINAL_BLOCKING_SESSION || chr(10)|| 
  --w'dbms_xplan :' ||'    select * from table(dbms_xplan.display_cursor('||''''||s.sql_id ||''''||',null,''ADVANCED OUTLINE ALLSTATS LAST +PEEKED_BINDS''));' ||CHR(10) ||
   'SQL_TEXT    : ' || q.sql_fulltext||CHR(10)||
  'dbms_xplan :' ||'   SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id =>'''||s.sql_id||''''||','|| 'cursor_child_no =>' ||child_number||','|| 'FORMAT =>'|| '''TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'' ));'  ||CHR(10)
FROM gv$session s,gv$process p,gv$sql q
where 1=1
and s.username IS NOT NULL 
and s.paddr  = p.addr
and s.inst_id  = p.inst_id
and s.inst_id  = q.inst_id
and s.sql_id = q.sql_id
AND NVL(q.sql_text,'x') NOT LIKE '%dummy_value%' 
--AND s.status != DECODE('&&active','n','xyz','N','xyz','INACTIVE')
and s.username IS NOT NULL
ORDER BY q.cpu_time;





set linesize 300 trimspool on pagesize 200
col event format a20
col USERNAME for a20
col SERNAME for a20
col kill for a17
col p2 for 99999999999999999
col P1TEXT for a20 
col P2TEXT for a20 
col P3TEXT for a20 
SELECT
''''||s.sid||','|| s.serial#||',@'||s.inst_id ||'''' kill
  , NVL(s.username,s.program) username
 --, s.sid                     sid
 --, s.serial#                 serial
 ,s.con_id
 ,s.sql_id
 , s.sql_hash_value          sql_hash_value
 , SUBSTR(DECODE(w.wait_time
               , 0, w.event
               , 'ON CPU'),1,15) event
 , w.p1                          p1
 , substr(w.P1TEXT,1,20) P1TEXT
 , w.p2                          p2
 ,substr(w.P2TEXT,1,20) P2TEXT
 , w.p3 p3
 ,substr(w.P3TEXT,1,20) P3TEXT
 from gv$session s, gv$session_wait  w
 where w.sid=s.sid
 and w.inst_id=s.inst_id
 and s.status='ACTIVE'
 AND s.type='USER'
 and username not in ('GGATE' ,'XXXX','SYS' ,'C##GGATE' )
 ;


set linesize 250 pagesize 60
select 	''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
s.con_id,
	s.username username,
	e.event event,
--	s.sid,
	s.sql_id,
	e.p1text,
	e.p1,
	e.p2text,
	e.p2,
	e.seq# seq,
	e.state,
	e.wait_time,
	-- time of current or most recent wait in microseconds
	--e.wait_time_micro,
	e.wait_time_micro / 1000000 wait_time_seconds,
	--e.seconds_in_wait, -- a confusing and somewhat useless column - see 11g docs for v$session
	decode(s.blocking_session, null,'',s.blocking_instance || ':' || s.blocking_session) blocking_session
from gv$session s, gv$session_wait e
where s.username is not null
	and s.sid = e.sid
and s.inst_id = e.inst_id
	-- skip sqlnet idle session messages
	--and s.module like 'Gathering Stats :EMT%'
and s.event not in ('SQL*Net message from client','SQL*Net more data from client','REPL Capture/Apply: messages','rdbms ipc message')
order by s.username, upper(e.event)
/

 

   
   set lines 170  pages 1000
col event format a35
select inst_id,event,count(*) from gv$session_wait
group by inst_id,event 
having count(*) >2
order by 3 desc
/
 



set echo off heading on feedback on
set linesize 200 trimspool on
set pagesize 60
set tab off

col blocking_sql_id format a12 head 'BLOCKING|SQL_ID'
col session_id format 999999 head 'SID'
col event format a40 head 'EVENT'
col session_state format a12 head 'SESSION|STATE'
col time_waited format 999,999.99 head 'TIME|WAITED|SECONDS'

col sample_time format a25 head 'SAMPLE TIME'


with blocked as (
	select distinct h.inst_id, h.sample_time, h.sample_id, h.session_id, h.session_serial#, h.blocking_session, h.blocking_session_serial#, h.sql_id, h.event, h.session_state
	from gv$active_session_history h
	where h.blocking_session is not null
	--and h.event like '&v_event_filter'
and sample_time > sysdate - INTERVAL '15' MINUTE   
),
blockers as (
	select distinct
		 max(b.sample_id) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_id
		, max(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) sample_time
		, count(b.sample_time) over (partition by b.inst_id, b.session_id, b.session_serial#) event_count
		, b.sql_id
		, b.inst_id
		, b.session_id
		, b.blocking_session
		, b.event
		, b.session_serial#
		, b.session_state
		-- NULL for top level blockers
		--, b.time_waited
	from blocked b
	left outer join gv$active_session_history bl
		on bl.sample_id = b.sample_id
		and bl.inst_id = b.inst_id
		and bl.sql_id = b.sql_id
		and bl.blocking_session = b.session_id
		and bl.blocking_session_serial# = b.blocking_session_serial#
)
select
	--sample_id
	sample_time
	, sql_id
	, session_id
	, blocking_session
	, inst_id
	, event_count
	, session_state
	, event
from blockers
order by sample_time, session_id
/

=======




set lines 200 verify  off  head off  feedback on long 5000
 select distinct
 '========================================================='					||chr(10)||
 --'Sid , Serial#     : '||S.Sid   ||' , '||S.Serial# ||','||s.inst_id 			||Chr(10)||
 'Sid , Serial#     : '||''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' 	||Chr(10)||
 'Server/Shadow     : '||P.Spid    												||Chr(10)||
 'Client/Foreground : '||S.Process   											||Chr(10)||
 'Terminal / Machine: '||S.terminal||' / '||S.Machine   						||Chr(10)||
 'Username........: '||S.Username   											||Chr(10)||
 'Osuser..........: '||S.Osuser   												||Chr(10)||
 'Program.........: '||S.Program    											||Chr(10)||
 'Module..........: '||S.Module    												||Chr(10)||
 'Status..........: '||S.Status  												||Chr(10)||
 'Action..........: '||S.Action   												||Chr(10)||
 'Wait_time.......: '||W.Wait_time   											||Chr(10)||
 'State ..........: '||W.State   												||Chr(10)||
 'Wait Event .....: '||W.Event   												||Chr(10)||
 'Seconds_in_wait.: '||W.Seconds_in_wait   										||Chr(10)||
 'Pga_alloc....: '|| To_char(P.Pga_alloc_mem/1024/1024,'9,999.99') ||' Mb'  	||Chr(10)||
 'Pga_used.....: '|| To_char(P.Pga_used_mem/1024/1024 ,'9,999.99') ||' Mb' 		||Chr(10)||
 'Pga_free.....: '|| To_char(P.Pga_freeable_mem/1024/1024,'9,999.99') ||' Mb'  	||Chr(10)||
 'Pga_max......: '|| To_char(P.Pga_max_mem/1024/1024,'9,999.99')      ||' Mb'  	||Chr(10)||
 'Lock / Latch.: '|| Nvl(S.Lockwait, 'None')||'/ '||Nvl(P.Latchwait, 'None')   	||Chr(10)||
 'Blocking Sessi=on: '||s.blocking_session										||Chr(10)||
 'Blocking Session Status: '|| s.blocking_session_status						||Chr(10)||
 'Latch Spin...: '|| Nvl(P.Latchspin, 'None')   								||Chr(10)||
 'Logon Time...: '|| To_char(S.Logon_time, 'Dy Dd-Mon-Yy Hh24:Mi:Ss')   		||Chr(10)||
 'Last Call....: '|| To_char(Sysdate-(S.Last_call_et/60/60/24), 'Dy Dd-Mon-Yy Hh24:Mi:Ss') || '  -> ' || To_char(S.Last_call_et/60,'99999.0') ||
 ' Mins' || To_char(S.Last_call_et/60/60, '99999.0') ||
 'Hours' ||To_char(S.Last_call_et/60/60/24, '99.0') || ' Days'  				||Chr(10)||
 'Sql Address. : '||S.Sql_address   											||Chr(10)||
 'Sql Hash.... : '||S.Sql_hash_value   											||Chr(10)||
 'Prev Sql Hash: '||S.Prev_hash_value   										||Chr(10)||
 'Trans Status : '|| Nvl(T.Status,'None') 										|| Chr(10)||
 'Trans Active : '|| Nvl(S.Taddr, 'None')										||Chr(10)||
 'Undo Generation: '||Nvl(T.Used_ublk,0) || ' Blocks'							||Chr(10)||
 'Changed Blocks : '||I.Block_changes||' Blocks'								||Chr(10)||
 '............    Current  Sql Statment   .................: '					||Chr(10)||
 '========================================================='					||Chr(10)||
Nvl(Q.Sql_text,'No Current Sql Statment')    									||Chr(10)||
 '========================================================='					||Chr(10)||
 '................  Prev Sql Statment  ..................: '					||Chr(10)||
 '========================================================='					||Chr(10)||
Nvl(Q2.Sql_text,'No Sql Statment')    ||Chr(10)||
 '========================================================='
 from gv$session s, gv$process p , gv$sqlarea q , gv$sqlarea q2,gv$session_wait w , gv$transaction t, gv$sess_io i
 where p.addr=s.paddr
 and s.sid=i.sid
 -- and s.sid=nvl('sid',s.sid)
 and s.sid=w.sid
 -- and p.spid=nvl('spid',p.spid)
 and q.HASH_VALUE(+)=s.sql_hash_value
 and q2.hash_value(+)=s.prev_hash_value
 and s.taddr=t.addr(+)
 --and s.sql_id in (select sql_id from gv$session where sql_id is not null and USERNAME='USER' )
and s.USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' ,'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
 -- and s.sql_id in (select sql_id from gv$session where sql_id is not null  )
 -- and 
 --and nvl(s.process,-1) = nvl('ClientPid',nvl(s.process,-1))
 ;
 

*****************************

set linesize 600 pagesize 300 
col SQL_TEXT for a50
col kill for a15
col USERNAME for a15 
col PLSQL_ENTRY_OBJECT   for a15
  col PLSQL_ENTRY_SUBPROGRAM for a20    
col PLSQL_ENTRY_SUBPROGRAM  for a20    

SELECT ''''||se.sid ||','|| se.serial#||',@'||se.inst_id ||'''' kill 
,      username
,      ( SELECT max( substr( sql_text , 1, 40 )) SQL_TEXT  FROM gv$sql sq WHERE sq.sql_id = se.sql_id ) AS sql_text
,      ( SELECT object_name    FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = 0) AS plsql_entry_object
,      ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_entry_object_id AND subprogram_id = plsql_entry_subprogram_id) AS plsql_entry_subprogram
,      ( SELECT object_name    FROM dba_procedures WHERE object_id = plsql_object_id       AND subprogram_id = 0)                         AS plsql_entry_object
,      ( SELECT procedure_name FROM dba_procedures WHERE object_id = plsql_object_id       AND subprogram_id = PLSQL_SUBPROGRAM_ID)       AS plsql_entry_subprogram
--,      se.*
,se.sql_id
FROM   gv$session se
WHERE  1=1
--AND    se.status = 'ACTIVE'
-- AND    sid = 369
AND    plsql_entry_object_id IS NOT NULL
and USERNAME not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
and sql_id='2u2jkj3sgctvn'
;

7 comments:

Anuj Singh said...: http://anuj-singh.blogspot.co.uk/2012/06/oracle-10g-active-session-set-pagesize.html; 2 January 2016 at 16:52
Anuj Singh said...: http://anuj-singh.blogspot.co.uk/2011/07/oracle-top-sql-spent-more-on-cpuwaitio.html; 3 January 2016 at 12:07
Anuj Singh said...: http://anuj-singh.blogspot.co.uk/2011/11/oracle-top-sql.html; 3 January 2016 at 12:08
Anuj Singh said...: http://anuj-singh.blogspot.co.uk/2010/05/oracle-top-sql-given-time.html; 3 January 2016 at 12:14
Anuj Singh said...: http://anuj-singh.blogspot.co.uk/2013/11/oracle-top-10-sql-waits.html; 3 January 2016 at 12:31
Anuj Singh said...: http://anuj-singh.blogspot.co.uk/2011_11_14_archive.html; 3 January 2016 at 13:32
Anuj Singh said...: http://anuj-singh.blogspot.com/2014/10/show-sql-for-users-in-rac-database-and.html; 4 September 2018 at 15:41

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Sunday, 8 March 2015

Oracle Session info

Session Info ...

7 comments:

Oracle DBA

anuj blog Archive