Oracle Database

Simple and Quick Way to Get SQL_ID of Query in Oracle

1 min read

Oct 4, 2013 12:00:00 AM

Notice

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.

Example

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