Oracle Flashback Table
Version 12.1.0.2

Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
 
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-SEP-13 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-SEP-13 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-SEP-13 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-SEP-13 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-SEP-13 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-SEP-13 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-SEP-13 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-SEP-13 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-SEP-13 12.51.52.050000');

SELECT owner, COUNT(*)
FROM t
GROUP BY owner;

FLASHBACK TABLEuwclass.t TO TIMESTAMP
TO_TIMESTAMP('02-SEP-13 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;

Related Topics
DBMS_FBT
Flashback Archive
Flashback Database
Flashback Drop
Flashback Query
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
Recycle Bin
Restore Points
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved