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;
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 ');
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;
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;
it only shows sql_id. how to get sql_text against that id too ?
ReplyDelete