Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

SQL text

How to find SQL text from SID and Serial# from a session that just became inactive?
0
msimons4
Asked:
msimons4
  • 3
4 Solutions
 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now