Flashback a table in oracle database

Oracle strongly recommends that you run your database in automatic undo mode by leaving
the UNDO_MANAGEMENT initialization parameter set to AUTO, which is the default. In addition,
set the UNDO_RETENTION initialization parameter to an interval large enough to include the
oldest data you anticipate needing.

For more information refer to the documentation on the UNDO_MANAGEMENT and UNDO_RETENTION
initialization parameters in ORACLE docs.
There are multiple methods to flashback a table in Oracle database. You do not need to
enable this flashback feature, it comes by default.

Note: TO FLASHBACK a table to its original, you will have to enable row movement.
ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT.

1. If you have dropped a table accidentally, we can flashback the table.

drop table print_media; FLASHBACK TABLE print_media TO BEFORE DROP;

2. To rename table on the fly when you flashback.

FLASHBACK TABLE print_media TO BEFORE DROP RENAME TO print_media_old;

3. Flashback table to its original table with interval in MINUTES.

FLASHBACK TABLE employees_test TO TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘1’ minute);

4. Flashback table to its original table with TIMESTAMP.

FLASHBACK TABLE emp TO TIMESTAMP TO_TIMESTAMP(‘2007-06-19 09:30:00′, `YYYY-MM-DD HH24:MI:SS’);

5. If you have passed the UNDO RETENTION period, there are chances to still flashback a table. Verify if you can flashback:

SELECT count(*) FROM emp AS OF TIMESTAMP TO_TIMESTAMP(‘2007-06-07 10:00:00’, ‘YYYY-MM-DD HH:MI:SS’);

If you see the result, you will be able to flashback else you will not be able to flashback.

6. Create a new table with flashback data.

create table new_table as select * from emp AS OF TIMESTAMP TO_TIMESTAMP (‘2007-06-07 10:00:00’, ‘YYYY-MM-DD HH:MI:SS’);

7. Check the versions of flashback content.

SQL> Connect / as sysdba SQL> column versions_starttime format a16 SQL> column versions_endtime format a16 SQL> set linesize 120; SQL> select versions_xid,versions_starttime,versions_endtime, versions_operation,empno,name,sal from emp versions between timestamp to_timestamp(?2007-06-19 20:30:00?,?yyyy-mm-dd hh:mi:ss?) and to_timestamp(?2007-06-19 21:00:00?,?yyyy-mm-dd hh:mi:ss?);

Related Posts

About The Author

Add Comment