How To Find The SQL ID Of The Statement You Just Ran?

Have you ever had to determine the SQL ID of the query you just ran?

I am not talking about finding the SQL ID of a query run in another session, or the SQL ID of a specific statement that ran 2 days ago.

No.

I am talking about the SQL ID of the statement you just ran a second ago!

I am sure you are familiar with the feeling of panic when you need to get the answer fast, and you can’t.
You look up the answer on the internet, only to find the proposed solution is not working for you.

Now what?

Well, maybe the query runs a long time and you are able to find your session in a different window. I call this luck. And I don’t like to rely on luck only.
However, if the query runs very fast, you won’t be able to find your “running” session in another window.

The solutions I found on the internet previously, would work if you knew about another thing, which was not mentioned at all in the posts.

The column PREV_SQL_ID of V$SESSION view, should be populated with the SQL ID of the statement that you just ran.

Let’s investigate below what happens:

sqlplus / as sysdba

SQL>select count(*) from dba_tables;

  COUNT(*)
----------
      1853

SQL>select prev_sql_id from v$session where sid=sys_context('userenv','sid');

PREV_SQL_ID
-------------
9babjv8yq8ru3

SQL>select count(*) from dba_indexes;

  COUNT(*)
----------
      2965

SQL>select prev_sql_id from v$session where sid=sys_context('userenv','sid');

PREV_SQL_ID
-------------
9babjv8yq8ru3

I just ran two completely different SQL statements, and the SQL IDs that were returned to me, were identical: 9babjv8yq8ru3.

How is that possible?

Let’s see what is the text of the SQL ID I just got above:

SQL>select sql_text from v$sqltext 
where sql_id = '9babjv8yq8ru3' order by piece;

SQL_TEXT
----------------------------------------------------------------
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;

This is not what I ran. What is happening here?

If serveroutput is ON, which by default is, then, the last statement your session ran is the hidden call to dbms_output, to flush the dbms_output buffer to the screen.

What is the solution, so we get the right answer?

Turn serveroutput OFF! It’s a very quick and easy answer!

Let’s see the answer in action below:

SQL>set serveroutput off
SQL>select count(*) from dba_tables;

  COUNT(*)
----------
      1853

SQL>select prev_sql_id from v$session where sid=sys_context('userenv','sid');

PREV_SQL_ID
-------------
56bs32ukywdsq

SQL>select count(*) from dba_indexes;

  COUNT(*)
----------
      2965

SQL>select prev_sql_id from v$session where sid=sys_context('userenv','sid');

PREV_SQL_ID
-------------
gd1vt5j4ntxph

SQL>select sql_id, sql_text from v$sqltext where sql_id in ('56bs32ukywdsq','gd1vt5j4ntxph') order by piece;

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
gd1vt5j4ntxph select count(*) from dba_indexes
56bs32ukywdsq select count(*) from dba_tables

The missing piece in some of the resources I found, was to set serveroutput off.

Here it is in clear text, what you need to do to get the SQL ID of the SQL statement you just ran:

set serveroutput off
select prev_sql_id from v$session where sid=sys_context('userenv','sid');

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana