Using Oracle Flash back to find data

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

Advertisements

Author: Abdul H Khan

Trying to be cloudy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s