Oracle | Flashback Table

Oracle Flashback Table Version 21c

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Flashback To SCN

Flashback to SCN Demo FLASHBACK TABLE <schema_name.table_name> TO SCN <scn_number> [<ENABLE | DISABLE> TRIGGERS];

CREATE TABLE t

      ENABLE ROW MOVEMENT AS

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE 1=2;

      

      desc t

      

      SELECT table_name, row_movement

      FROM user_tables;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM  v$database;

      -- 5607529 02-MAR-18 12.46.50.906000 PM -07:00

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'SYS';

      

      COMMIT;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM  v$database;

      -- 5607537 02-MAR-18 12.47.06.453000 PM -07:00

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'WMSYS';

      

      COMMIT;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM  v$database;

      -- 5607545 02-MAR-18 12.47.25.359000 PM -07:00

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'CTXSYS';

      

      COMMIT;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM  v$database;

      -- 5607552 02-MAR-18 12.47.38.187000 PM -07:00

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      conn / as sysdba

      

      set linesize 121

      col owner format a10

      

      FLASHBACK TABLE uwclass.t TO SCN 5607547;

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      FLASHBACK TABLE uwclass.t TO SCN 5607540;

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

Flashback To TIMESTAMP

Flashback to Timestamp Demo FLASHBACK TABLE <schema_name.table_name> TO TIMESTAMP <timestamp> [<ENABLE | DISABLE> TRIGGERS];

CREATE TABLE t

      ENABLE ROW MOVEMENT AS

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE 1=2;

      

      desc t

      

      SELECT table_name, row_movement

      FROM user_tables;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM v$database;

      -- 5607702 02-MAR-18 12.51.33.390000 PM -07:00

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'SYS';

      

      COMMIT;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM v$database;

      -- 5607709 02-MAR-18 12.51.46.187000 PM -07:00

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'WMSYS';

      

      COMMIT;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM v$database;

      -- 5607716 02-MAR-18 12.52.00.562000 PM -07:00

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'CTXSYS';

      

      COMMIT;

      

      SELECT current_scn, SYSTIMESTAMP

      FROM v$database;

      -- 5607722 02-MAR-18 12.52.13.359000 PM -07:00

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      conn / as sysdba

      

      set linesize 121

      col owner format a10

      

      FLASHBACK TABLE uwclass.t TO TIMESTAMP

      TO_TIMESTAMP('02-MAR-18 12.51.52.050000');

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      FLASHBACK TABLEuwclass.t TO TIMESTAMP

      TO_TIMESTAMP('02-MAR-18 12.51.51.500000');

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

Flashback To Restore Point

Flashback to Restore Point Demo FLASHBACK TABLE <schema_name.table_name> TO RESTORE POINT <restore_point> [<ENABLE | DISABLE> TRIGGERS];

CREATE TABLE t

      ENABLE ROW MOVEMENT AS

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE 1=2;

      

      desc t

      

      SELECT table_name, row_movement

      FROM user_tables;

      

      CREATE RESTORE POINT zero;

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'SYS';

      

      COMMIT;

      

      CREATE RESTORE POINT one;

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'WMSYS';

      

      COMMIT;

      

      CREATE RESTORE POINT two;

      

      INSERT INTO t

      SELECT owner, table_name, tablespace_name

      FROM all_tables

      WHERE owner = 'CTXSYS';

      

      COMMIT;

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      SELECT scn, time, name

      FROM gv$restore_point;

      

      FLASHBACK TABLE t TO RESTORE POINT two;

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      FLASHBACK TABLE t TO RESTORE POINT one;

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;

      

      FLASHBACK TABLE t TO RESTORE POINT zero;

      

      SELECT owner, COUNT(*)

      FROM t

      GROUP BY owner;