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