oracle flashback - ghdrako/doc_snipets GitHub Wiki

Oracle Flashback Table

Oracle Flashback Table enables you to rewind one or more tables back to their

Enabling Row Movement on a Table

Row movement indicates that rowids will change after the flashback occurs. This restriction exists because if rowids before the flashback were stored by an application, then there is no guarantee that the rowids will correspond to the same rows after the flashback.

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;

Performing a Flashback Table Operation In

• determine the dependencies for the hr.employees:

SELECT other.owner, other.table_name  
FROM sys.all_constraints this, sys.all_constraints other  
WHERE this.owner = 'HR'  
  AND this.table_name = 'EMPLOYEES'  
  AND this.r_owner = other.owner  
  AND this.r_constraint_name = other.constraint_name  
  AND this.constraint_type='R';

• Ensure that row movement is enabled for the table that you want to flash back and its dependent tables.
• Identify the time, SCN, or restore point to which you want to return the table.
• Verify that enough undo data exists to rewind the table to the specified target.

SELECT NAME, VALUE/60 MINUTES_RETAINED  
FROM V$PARAMETER 
WHERE NAME = 'undo_retention';

NAME MINUTES_RETAINED 
--------------- ----------------
undo_retention 15 6.

• Use the FLASHBACK TABLE statement to perform a flashback operation for the required tables.

FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');
FLASHBACK TABLE hr.jobs TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');
FLASHBACK TABLE hr.departments TO TIMESTAMP TO_TIMESTAMP('2012-03-27 09:30:00', 'YYYY-MM-DD HH:MI:SS');

Recovering a Dropped Table Using Oracle Flashback Drop

Oracle Flashback Drop enables you to reverse the effects of dropping (deleting) a table, returning the dropped table to the database along with dependent objects such as indexes and triggers. This feature stores dropped objects in a recycle bin, from which they can be retrieved until the recycle bin is purged, either explicitly or because space is needed. As with Flashback Table, you can use Flashback Drop while the database is open. Also, you can perform the flashback without undoing changes in objects not affected by the Flashback Drop operation.

CREATE TABLE reg_hist as SELECT * FROM REGIONS;
DROP TABLE REG_HIST;
SELECT * FROM TAB;
SHOW RECYCLEBIN
FLASHBACK TABLE HR.REG_HIST TO BEFORE DROP;
-- manuał re-create referential constraints

Rewinding a Database Using Oracle Flashback Database

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE to TIME "TO_DATE('03/20/12','MM/DD/YY')";
ALTER DATABASE OPEN READ ONLY; --some queries to verify the database contents.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN RESETLOGS;

Flashback query

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY/MM/DD HH24:MI:SS';

  select count(*) from dual
  AS OF TIMESTAMP TO_TIMESTAMP('2010/01/01 00:00:00');