Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL text

Posted on 2011-03-24
5
Medium Priority
?
425 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 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 1500 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 500 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 1500 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

609 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