Tuesday 7 August 2018

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; 

1 comment:

  1. it only shows sql_id. how to get sql_text against that id too ?

    ReplyDelete