Two Easy Ways to Get SQL_ID in Your Current Session

In the last blog post, How To Find the SQL_ID of a SQL Statement, we talked about finding the SQL_ID of statements that were run in the past, or by other users. Today I’ll show you two tricks to get the same information, if you can run the statements in your own session. There are two easy ways to get the SQL_ID of a SQL Statement that you execute in your session. The first method is so easy, you won’t believe it!

1. Starting with version 18c, a simple command is available to you in sqlplus:

SET FEEDBACK ON SQL_ID

Once you run this command, when your statement executes, besides the number of rows returned message, you will also see the SQL_ID:

SQL> SET FEEDBACK ON SQL_ID
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

1 row selected.

SQL_ID: 3ghpkw4yp4dzm

You see at the bottom SQL_ID: 3ghpkw4yp4dzm returned. This is brilliant and helpful!

2. Another way to get the SQL_ID is from the V$SESSION view, column PREV_SQL_ID. This shows you the last SQL_ID that a session executed. And if you look for your own session, then you find the SQL_ID you just ran.

But before running your statement, you must run something else, otherwise you will not receive good results. If serveroutput is ON, and by default it is, then the last statement your session ran is a hidden call to dbms_output, to flush the dbms_output buffer to the screen.
The solution to this is to set serveroutput off. Let’s see an example:

SQL> set serveroutput off
SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107

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

PREV_SQL_ID
-------------
3ghpkw4yp4dzm

As you see, the SQL_IDs returned by the two methods are identical, as they should be! This last method works in any version of Oracle!

The caveat to these two methods is, that these methods do not help you, if you are looking for a SQL_ID that ran in the past, or ran through the application, or by someone else. These two methods are only useful if you can and want to run the SQL Statement in your session, in sqlplus.

Free Video Tutorial