Query to find the SQL_ID from SQL_TEXT | Oracle Database

 

Query to find the SQL_ID from SQL_TEXT | Oracle Database

How to find the SQL_ID from SQL Text?

Below mentioned queries can help you find the SQL_ID if you have the SQL_TEXT for the same. You can use any of them depending upon the information you want to get.

1) 

SELECT SQL_ID, SQL_FULLTEXT
FROM v$sqlarea
WHERE substr(sql_text,1,40) = 'select t2.service_num as ServiceId, t3.n'
AND ROWNUM <= 20;

2)

select SESSION_ID, SQL_ID, USER_ID, 
PROGRAM, MACHINE, DELTA_TIME, SQL_PLAN_HASH_VALUE, SERVICE_HASH
from DBA_HIST_ACTIVE_SESS_HISTORY
where sql_id in (SELECT SQL_ID
FROM gv$sqlarea
WHERE substr(sql_text,1,40) = 'SELECT C.OU_NUM AS BA_NUM, A.CREATED AS '); 

3)

select du.username, PROGRAM, MACHINE
from DBA_HIST_ACTIVE_SESS_HISTORY DH, dba_users du
where DH.sql_id in (SELECT SQL_ID
FROM gv$sqlarea
WHERE substr(sql_text,1,40) = 'select t2.service_num as ServiceId, t3.n')
and dh.user_id = du.user_id
group by du.username, PROGRAM, MACHINE;