How-to show oracle active sessions

Save it to a SQL file named a.sql

col event format a24
col sid format 99999
set line 200
col machine format a30
col username format a10
col w format 999
col status format a2
col event format a30
col p3 format 999
col wt format 99999
col lt format 9999999
col param format a25
col SQL format a24
select b.sid,
  substr(b.username,1,10) username,
  decode(program, null, machine,replace(program,' (TNS V1-V3)','')||decode(machine,null,'@'||terminal)) machine,
  substr((case
    when a.event like 'enq%'
      then a.event|| ':' || chr(bitand(a.p1,-16777216)/16777215)||chr(bitand(a.p1, 16711680)/65535)||'-'||bitand(a.p1, 65535)||':'||ROW_WAIT_FILE#||':'||ROW_WAIT_BLOCK#||':'|| ROW_WAIT_ROW#
    else a.event
    end),1,30) event,
  a.p1||'/'||a.p2||'/'||a.p3 param,
  a.wait_time w,a.SECONDS_IN_WAIT WT,b.sql_hash_value||'/'||b.PREV_HASH_VALUE SQL, 
  decode(b.status,'ACTIVE','A','INACTIVE','I','KILLED','K',STATUS) status,
  last_call_et LT,
  (sysdate -logon_time )*24*3600 LOGON_TIME
from v$session_wait a,v$session b
where ((a.event not like 'SQL*Net message%' and a.event not like 'rdbms%') or b.status='ACTIVE') and a.sid=b.sid
and b.type='USER'
and b.sid <>(SELECT SID FROM V$MYSTAT WHERE ROWNUM=1)
order by sql_hash_value;

Run it and get this kind of output:

SYS@DB: SQL> @a       

   SID USERNAME   MACHINE                        EVENT                          PARAM                        W     WT SQL                      ST       LT LOGON_TIME
------ ---------- ------------------------------ ------------------------------ ------------------------- ---- ------ ------------------------ -- -------- ----------
  1049 SYS        oracle@tstdb30 (P005)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4464       4489
  1081 SYS        oracle@tstdb30 (P006)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4464       4489
   956 SYS        oracle@tstdb30 (P007)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4465       4489
  1016 SYS        oracle@tstdb30 (P002)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4465       4489
   993 SYS        oracle@tstdb30 (P000)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4465       4489
   963 SYS        oracle@tstdb30 (P004)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4464       4489
   974 SYS        oracle@tstdb30 (P001)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4464       4489
   999 AP_USER    dev-ss01                       log file sync                  5063/0/0                     0      0 0/1572343016             A         0       1468
   960 SYS        oracle@tstdb30 (P003)          PX Deq: Execution Msg          268566527/2232/0             0      2 0/0                      I      4464       4489
   955 AP_USER    sqlplus@apphost                enq: TX - row lock contention: 1415053318/524290/867587     0  39810 3072680396/3008127692    A     39980      46035
  1023 AP_USER    sqlplus@apphost                db file sequential read        94/571025/1                  1   2798 3072680396/3008127692    A    125548     132435
  1025 SYS        sqlplus@tstdb30                db file sequential read        166/850825/1                 0      0 3323843464/0             A        10         10
   952 AP_USER    sqlplus@apphost                enq: TX - row lock contention: 1415053318/655395/2848374  250  43857 3459093128/3008127692    A     43922      45974
   992 AP_USER    sqlplus@apphost                latch: cache buffers chains    117982335952/122/0          -1     11 3459093128/3459093128    A    129647     132373
   959 SYS        sqlplus@tstdb30                db file sequential read        148/379695/1                 0      0 4154924025/1914538543    A      4600      21422

15 rows selected.
Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How-to show oracle active sessions

  1. Seo Services says:

    This website truly has all of the information I wanted concerning
    this subject and didn’t know who to ask.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: