Using Oracle Flash back to find data

Reading Time: < 1 minute

Here are few SQL statemnets that can be used to lookup data using Oracle table version by timestap:

–select for table with version

select
* from PSOPRDEFN as of timestamp TO_TIMESTAMP(‘2009-05-19 21:24:02’, ‘YYYY-MM-DD HH24:MI:SS’)

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
oprid ,VERSION,OPRDEFNDESC
from PSOPRDEFN
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP(‘2009-05-19 20:00:08’, ‘YYYY-MM-DD HH24:MI:SS’)
AND TO_TIMESTAMP(‘2009-05-19 21:30:00’, ‘YYYY-MM-DD HH24:MI:SS’)
where oprid=’XXXX’;

select *
from dba_audit_trail
where timestamp between
TO_DATE(’05/19/2009:20:50:00′, ‘MM/DD/YYYY:HH24:MI:ss’) AND
TO_DATE(’05/19/2009:21:25:00′, ‘MM/DD/YYYY:HH24:MI:ss’)
and os_username not in (‘psoft’, ‘root’)
and username ‘xxxx’ –Users that you don’t want to show on report.
and action_name ‘LOGOFF’ — same here where ACTION is != to ‘LOGOFF’.
order by timestamp

Extented Audit Trails:

select * From DBA_COMMON_AUDIT_TRAIL where extended_timestamp between
TO_DATE(’07/29/2009:16:35:00′, ‘MM/DD/YYYY:HH24:MI:ss’) AND
TO_DATE(’07/29/2009:16:35:49′, ‘MM/DD/YYYY:HH24:MI:ss’)
order by extended_timestamp desc

for your refrence pleasure: http://www.petefinnigan.com/papers/audit.sql

Posted in SQL

Leave a Reply

Your email address will not be published. Required fields are marked *