Simple and Quick Way to Get SQL_ID of Query in Oracle

Oct 4, 2013 / By Fahd Mirza

Tags: ,

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 :)

One Response to “Simple and Quick Way to Get SQL_ID of Query in Oracle”

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>