Get SQL_ID for my Statement

An old trick to find the SQL_ID or hash_value (used more in the old days) for a statement is write a comment on the sentence to run, more recognizable and more difficult to match with others statements, then we need to seek the statement in the library cache, doing a search on the v$sqlarea or more restricted using the v$open_cursor, of course, there are many more ways to get this value, but this simple trick is very easy and fast.

Generate the establishment with the comment. You can use multiline comments or comments single line, in this case we use multiline

 

SELECT /* OMEGA */  * FROM DUAL;

 

We now proceed to seek the statement

 

SELECT SQL_ID, ADDRESS, PARSING_USER_ID, SQL_TEXT FROM
V$SQLAREA WHERE SQL_TEXT LIKE ‘%OMEGA%’;

 

This will give an output with two tuples, one of which is the statement that we executed and the other is the search query.

HTH – Antonio NAVARRO

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s