I'm running queries against our reporting world, analyzing what queries our users use (by comments appended to SQL), and I have a dumb question - how can I tell what the date range is for these queries? I'm filtering on SAMPLE_TIME, but I get the impression that has little to do with how far back these queries have been saved. Any suggestions where to look for the retention period for these views? I want to share this with our support team, but without a timeframe, it is not nearly as helpful.
Here is the sql I use for parsing my SQL - the key is the session / sqltext views. Any help would be appreciated, Brent
select to_char(sample_time,'yyyymmdd hh:mm:ss')||';' as query_time,
rtrim(replace(substr(dbms_lob.substr(b.sql_text,4000,1),INSTR(dbms_lob.substr(b.sql_text,4000,1), 'PnPv2 -',1,1),150),'*/',';')) REPORT,
';'||substr(dbms_lob.substr(b.sql_text,4000,1),INSTR(dbms_lob.substr(b.sql_text,4000,1), 'UserID:',1,1),150) USERNAME
from dba_hist_active_sess_history a, dba_hist_sqltext b
where sample_time between to_date('20150101:06:00','yyyymmdd:hh24:mi')
and to_date('20160930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
and dbms_lob.substr(b.sql_text,4000,1) like '%PnP%'
union all
select to_char(sample_time,'yyyymmdd hh:mm:ss')||';' as query_time,
rtrim(replace(substr(dbms_lob.substr(b.sql_text,4000,1),INSTR(dbms_lob.substr(b.sql_text,4000,1), 'PnPv2 -',1,1),150),'*/',';')) REPORT,
';'||substr(dbms_lob.substr(b.sql_text,4000,1),INSTR(dbms_lob.substr(b.sql_text,4000,1), 'UserID:',1,1),150) USERNAME
from v$active_session_history a ,v$sqlarea b
where sample_time between to_date('20150101:06:00','yyyymmdd:hh24:mi') and
to_date('20160930:09:01','yyyymmdd:hh24:mi') and b.sql_id=a.sql_id
and dbms_lob.substr(b.sql_text,4000,1) like '%PnP%';