Re: SQL_ID missing from v$session when session is "ACTIVE"

Hi Kyle,

V$ tables are not protected by ACID consistency rules. It is entirely possible to have some fields not populated in time of the query. You may extract the DDL for V$_SESSION view and use the query with out restrictions on some fields. I have done it a long time ago, with version 10, but that got lost in time.

Regards

I'm used to SQL_ID missing from v$session when it's a background process like LGWR but for user sessions I always expect to find a SQL_ID for a session that is "ACTIVE".

Anyone know of examples of why an user session would be missing SQL_ID when ACTIVE?

select sid||':'||serial# session_id,
       username,
       s.sql_id||':'||sql_child_number sqlid,
       SQL_ADDRESS,
       SQL_HASH_VALUE,
       command,
       decode(state, 'WAITING', wait_class||':'||event, 'CPU') event
from v$session s
where
            (( s.wait_time != 0  /* on CPU  */ and s.status='ACTIVE'  /* ACTIVE */)
                 or
                 s.wait_class  != 'Idle'
            )
/

SESSION_ID USERNAME

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

SQLID        SQL_ADDRESS

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

SQL_HASH_VALUE   COMMAND

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

EVENT

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

36:13933   KYLELF

:        00

     0 0

SQL*Net message from client

CPU

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com