Search This Blog

Total Pageviews

Thursday, 13 July 2023

How do I find my own session's serial# ?



How do I find my own session's serial# ?


My session 
col USERNAME  for a20
col event          for a30
select sid, serial#, username ,sql_id,event from v$session   where sid = ( select sid from v$mystat where rownum = 1);

===
set echo off feedback off heading on timi off pages 1000 lines 500 VERIFY OFF

col event          for a30
col wait_class     for a30
col waits          for 999,999,999
col time_waited_cs for 999,999,999
col avg_wait_cs    for 990.999
col USERNAME  for a20
select 
s.sid, s.serial#, s.CON_ID,s.username ,s.sql_id,
e.event,
       e.wait_class,
       sum(e.total_waits)  as waits,
       sum(e.time_waited)  as time_waited_cs,
       avg(e.average_wait) as avg_wait_cs
  from V$session_event e,v$session s
 where 1=1
and e.sid = sys_context('USERENV', 'SID')
   and e.wait_class <> 'Idle'
and e.sid=s.sid
 group by s.sid, s.serial#, s.CON_ID,s.username ,s.sql_id,e.event, e.wait_class
-- having sum(e.time_waited) > 0
 order by 4 desc
/

       SID    SERIAL#     CON_ID USERNAME             SQL_ID        EVENT                          WAIT_CLASS                            WAITS TIME_WAITED_CS AVG_WAIT_CS
__________ __________ __________ ____________________ _____________ ______________________________ ______________________________ ____________ ______________ ___________
       366      38442          0 SYS                  7kmh07qmcutq2 Disk file operations I/O       User I/O                                 24                   0       0.010
       366      38442          0 SYS                  7kmh07qmcutq2 SQL*Net break/reset to client  Application                              14                   0       0.000
       366      38442          0 SYS                  7kmh07qmcutq2 SQL*Net message to client      Network                                  26                   0       0.000
       366      38442          0 SYS                  7kmh07qmcutq2 control file sequential read   System I/O                                7                   1       0.070
       366      38442          0 SYS                  7kmh07qmcutq2 events in waitclass Other      Other                                   146                   7       0.050
SQL>

No comments:

Oracle DBA

anuj blog Archive