Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Length of retention in dba_hist_active_sess_history, dba_hist_sqltext

BrentWorksterMar 31 2016 — edited Mar 31 2016

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%';

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 28 2016
Added on Mar 31 2016
3 comments
1,717 views