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
where age > 78 order by name;
COL SQL_TEXT format a45
select /* MYCOMMENT1 */ sql_id, substr(sql_text,1,200) sql_text
where upper(sql_text) like ‘%MYCOMMENT%’
and sql_text not like ‘%/* MYCOMMENT1 */%’ ;
Enjoy query fishing :)
Interested in working with Fahd? Schedule a tech call.