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

Some DDL won’t appear there, I’ve seen this with grants especially.

Funnily enough, my first suggestion to Nenad in a recent thread would be my first go to if you want to see the SQL (and that chain has a bunch of other more advanced methods)

Oradebug setospid <spid>
Oradebug current_sql

Hope that helps,
Andrew

On Thu, 22 Jul 2021 at 16:36, kyle Hailey <kylelf_at_gmail.com> wrote:

>
>
> 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
>
>

--
http://www.freelists.org/webpage/oracle-l