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;
