Simple and Quick Way to Get SQL_ID of Query in Oracle

Posted in: Technical Track

If you need to get SQL_ID of a query from a busy system, which has similar queries scattered all around, it becomes a hassle to get what you are looking for. If the query for which you are getting SQL_ID is big, or contains lots of apostrophes or other not-so-nice characters, then it becomes more cumbersome.

The most simple way to get SQL_ID of query is to add comment in the query text and then get the SQL_ID from v$SQL view on the basis of that comment. Here is a working example:

select /* MYCOMMENT */ name,age,salary
from user.mytable
where age > 78 order by name;

COL SQL_TEXT format a45

select /* MYCOMMENT1 */ sql_id, substr(sql_text,1,200) sql_text
from v$sql
where upper(sql_text) like ‘%MYCOMMENT%’
and sql_text not like ‘%/* MYCOMMENT1 */%’ ;

Enjoy query fishing :)

Interested in working with Fahd? Schedule a tech call.

About the Author

I am in love with Oracle blogging since 2007. The blogging coupled with extensive participation in Oracle forums, plus Oracle related speaking engagements, various Oracle certifications, teaching, and working in the trenches with Oracle technologies enabled me to get Oracle ACE award. I was the first ever Pakistani to get that award. From Oracle Open World SF to Foresight 20:20 Perth, I have been expressing my love for Exadata. These days for the last some years, I am loving the data at Pythian, and proudly writing their log buffer carnivals.

1 Comment. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *