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

Advertisements

9 Responses to Oracle 11g Flashback examples

  1. Ian Williams says:

    G’day Matthias. Curious as to why you used RMAN in the last example of flashback database.

    You can issue those flashback commands from within SQLPLUS if desired.

  2. Kishore says:

    Nice examples on timestamp, Thanks

  3. Christian says:

    It works well. But only works on big tables, if the undo tabespace is big enough. I only use it to correct data after wrong sql-statements ;) for better history i use an extra table for logging datachanges

  4. Venu says:

    Your queries where very useful. Thank you :)

  5. Pingback: Confluence: Engineering

  6. best & usefull examples…thanks

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

%d bloggers like this: