SQL text

How to find SQL text from SID and Serial# from a session that just became inactive?
msimons4Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
If a session is inactive it isn't executing any SQL and it might not show.

There is a Check out V$SQL.  There is a SQL_FULLTEXT and SQL_ID column

V$SESSION has a SQL_ID and PREV_SQL_ID (if sql_id doesn't have anything for 'inactive' sessions).

Just join on the ID field.
0
 
msimons4Author Commented:
Does someone have a query to get SQL text from an inactive session?
0
 
slightwv (䄆 Netminder) Commented:
Are you asking about an INACTIVE status in v$session?

Did you look at the SQL_ID and PREV_SQL_ID columns for one of the 'inactive' status session to see what the values were?
0
 
Le_RoccaCommented:
You can try this

select
   a.sid,
   a.serial#,
   b.sql_text
from
   v$session a,
   v$sqlarea b
where
   a.sql_address=b.address
and
   a.username='GRUMPY';
0
 
slightwv (䄆 Netminder) Commented:
>>b.sql_text

That is only the first 1000 characters of the SQL.

In newer versions use the SQL_FULLTEXT column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.