Oracle Flashback Query

Flashback by SCN

SELECT <column_name_list>
FROM <table_name>
AS OF <SCN>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
conn uwclass/uwclass

CREATE TABLE t AS
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE 1=2;

desc t

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604676 18-AUG-15 10.54.39.218000 AM -10:00

INSERT INTO t VALUES ('A', 'AAAAA', 'AAAAAAAAAA');

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604687 18-AUG-15 10.55.01.984000 AM -10:00

INSERT INTO t VALUES ('B', 'BBBBB', 'BBBBBBBBBB');

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604694 18-AUG-15 10.55.14.421000 AM -10:00

INSERT INTO t VALUES ('C', 'CCCCC', 'CCCCCCCCCC');

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604700 18-AUG-15 10.55.26.218000 AM -10:00

SELECT * FROM t;

sqlplus / as sysdba

set linesize 121
col owner format a10

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF SCN 5604684;

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF SCN 5604685;

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF SCN 5604691;

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF SCN 5604692;

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF SCN 5604697;

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF SCN 5604698;

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('18-AUG-15 10.55.20.000000');

Flashback by Timestamp

ELECT <column_name_list>
FROM <table_name>
AS OF <TIMESTAMP>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>]
conn uwclass/uwclass

CREATE TABLE t AS
SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE 1=2;

desc t

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604676 18-AUG-15 10.54.39.218000 AM -10:00

INSERT INTO t VALUES ('A', 'AAAAA', 'AAAAAAAAAA');

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604687 18-AUG-15 10.55.01.984000 AM -10:00

INSERT INTO t VALUES ('B', 'BBBBB', 'BBBBBBBBBB');

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604694 18-AUG-15 10.55.14.421000 AM -10:00

INSERT INTO t VALUES ('C', 'CCCCC', 'CCCCCCCCCC');

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604700 18-AUG-15 10.55.26.218000 AM -10:00

SELECT * FROM t;

sqlplus / as sysdba

set linesize 121
col owner format a10

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('18-AUG-15 10.55.00.000000');
Flashback by SCN
SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('18-AUG-15 10.55.10.000000');

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('18-AUG-15 10.55.20.000000');

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('18-AUG-15 10.55.40.000000');


AS OF Demo

CREATE TABLE t AS
SELECT *
FROM dba_objects
WHERE 1=2;

SELECT COUNT(*)
FROM t;

SELECT current_scn
FROM v$database;

INSERT INTO t
SELECT *
FROM dba_objects
WHERE rownum < 101;

COMMIT;

SELECT current_scn
FROM v$database;

INSERT INTO t
SELECT *
FROM dba_objects
WHERE rownum < 1001;

COMMIT;

SELECT current_scn
FROM v$database;

INSERT INTO t
SELECT *
FROM dba_objects
WHERE rownum < 10001;

COMMIT;

SELECT current_scn
FROM v$database;

SELECT COUNT(*)
FROM t
AS OF SCN 15263767;

SELECT COUNT(*)
FROM t
AS OF SCN 15263775;

SELECT COUNT(*)
FROM t
AS OF SCN 15263786;

SELECT COUNT(*)
FROM t

AS OF SCN 15263804;