SQL text

Posted on 2011-03-24
Medium Priority
Last Modified: 2012-05-11
How to find SQL text from SID and Serial# from a session that just became inactive?
Question by:msimons4
  • 3
LVL 78

Accepted Solution

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.

Author Comment

ID: 35210466
Does someone have a query to get SQL text from an inactive session?
LVL 78

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?

Assisted Solution

Le_Rocca earned 500 total points
ID: 35210547
You can try this

   v$session a,
   v$sqlarea b
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1500 total points
ID: 35210565

That is only the first 1000 characters of the SQL.

In newer versions use the SQL_FULLTEXT column.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

597 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