Solved

SQL text

Posted on 2011-03-24
5
412 Views
Last Modified: 2012-05-11
How to find SQL text from SID and Serial# from a session that just became inactive?
0
Comment
Question by:msimons4
  • 3
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 375 total points
ID: 35210372
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
 

Author Comment

by:msimons4
ID: 35210466
Does someone have a query to get SQL text from an inactive session?
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 35210533
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
 
LVL 1

Assisted Solution

by:Le_Rocca
Le_Rocca earned 125 total points
ID: 35210547
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 375 total points
ID: 35210565
>>b.sql_text

That is only the first 1000 characters of the SQL.

In newer versions use the SQL_FULLTEXT column.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Queries 15 48
sort a spool into file output in oracle 1 45
oracle DR - data guard failover. 18 48
oracle forms question 22 42
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question