Oracle 11g Flashback examples

Environment: Oracle database 11.2.0.3.0 Enterprise Edition.

This is a quick overview (mostly for myself ;-)) of the flashback features that are available with Oracle 11g.

— query table data from specific point in time

SELECT * FROM matthiash
AS OF TIMESTAMP
TO_TIMESTAMP('2013-05-03 14:00:00', 'YYYY-MM-DD HH24:MI:SS');

— query table data from one hour ago

SELECT * FROM matthiash
AS OF TIMESTAMP
(SYSTIMESTAMP - INTERVAL '60' MINUTE);

— query all versions of one or more records

SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,m_id
FROM matthiash
VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2013-05-03 14:45:00', 'YYYY-MM-DD HH24:MI:SS')

— get the user name of the user who modified records

GRANT SELECT ANY TRANSACTION TO matthiash;

SELECT xid, logon_user
FROM flashback_transaction_query
WHERE xid IN (
SELECT versions_xid FROM matthiash VERSIONS BETWEEN TIMESTAMP
TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2013-05-03 14:45:00', 'YYYY-MM-DD HH24:MI:SS')
)
AND xid = HEXTORAW('08000800C6070000');

— flashback entire table to specific point in time

ALTER TABLE matthiash ENABLE ROW MOVEMENT

FLASHBACK TABLE matthiash
TO TIMESTAMP TO_TIMESTAMP('2013-05-03 13:00:00', 'YYYY-MM-DD HH24:MI:SS');

— creating restore point and flashback table to restore point

CREATE RESTORE POINT before_table_update;

SELECT NAME, SCN, TIME
FROM V$RESTORE_POINT;

UPDATE matthiash SET m_id=0;

FLASHBACK TABLE matthiash.matthiash
TO RESTORE POINT before_table_update;

— recover dropped table from recycle bin (note: does not work for all types of tables)

DROP TABLE matthiash;

SELECT * FROM recyclebin;

FLASHBACK TABLE matthiash TO BEFORE DROP;

FLASHBACK TABLE "BIN$29FWNr5ICjbgQ68BEqzYLw==$0" TO BEFORE DROP RENAME TO matthiash;

— recover entire database to restore point, SCN or point in time

CREATE RESTORE POINT before_table_drop GUARANTEE FLASHBACK DATABASE;

DROP TABLE matthiash PURGE;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

FLASHBACK DATABASE TO RESTORE POINT BEFORE_TABLE_DROP;

FLASHBACK DATABASE TO SCN 46963;

FLASHBACK DATABASE TO TIME "TO_TIMESTAMP('2013-05-03 15:00:00', 'YYYY-MM-DD HH24:MI:SS')";

ALTER DATABASE OPEN RESETLOGS;

Matthias