Pythian Blog: Technical Track

Simple and Quick Way to Get SQL_ID of Query in Oracle

by Fahd Mirza on Oct 4, 2013 12:00:00 AM

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

No Comments Yet

Let us know what you think

See all

See all

Subscribe by email
Follow us
Love Your Data
Pythian Logo

© 2024 All rights reserved.