Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
AS OF SCN
Flashback by SCN
SELECT <column_name_list>
FROM <table_name>
AS OF <SCN> <scn_expression_yielding>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>];
conn uwclass/uwclass@pdbdev
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 20-DEC-20 10.54.39.218000 AM -07:00
INSERT INTO t VALUES ('A', 'AAAAA', 'AAAAAAAAAA');
COMMIT;
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604687 20-DEC-20 10.55.01.984000 AM -07:00
INSERT INTO t VALUES ('B', 'BBBBB', 'BBBBBBBBBB');
COMMIT;
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604694 20-DEC-20 10.55.14.421000 AM -07:00
INSERT INTO t VALUES ('C', 'CCCCC', 'CCCCCCCCCC');
COMMIT;
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604700 20-DEC-20 10.55.26.218000 AM -07:00
SELECT * FROM t;
conn sys@pdbdev 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;
AS OF TIMESTAMP
Flashback by Timestamp
SELECT <column_name_list>
FROM <table_name>
AS OF <TIMESTAMP> <timestamp_yielding_expression>
[WHERE <filter_conditions>]
[GROUP BY <unaggregated columns>]
[HAVING <group_filter>]
[ORDER BY <column_positions_or_name>];
conn uwclass/uwclass@pdbdev
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 20-DEC-20 10.54.39.218000 AM -07:00
INSERT INTO t VALUES ('A', 'AAAAA', 'AAAAAAAAAA');
COMMIT;
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604687 20-DEC-20 10.55.01.984000 AM -07:00
INSERT INTO t VALUES ('B', 'BBBBB', 'BBBBBBBBBB');
COMMIT;
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604694 20-DEC-20 10.55.14.421000 AM -07:00
INSERT INTO t VALUES ('C', 'CCCCC', 'CCCCCCCCCC');
COMMIT;
SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604700 20-DEC-20 10.55.26.218000 AM -07:00
SELECT * FROM t;
conn sys@pdbdev as sysdba
set linesize 121
col owner format a10
SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('20-DEC-20 10.55.00.000000');
SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('20-DEC-20 10.55.10.000000');
SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('20-DEC-20 10.55.20.000000');
SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('20-DEC-20 10.55.40.000000');
Using a variable to allow repeated use of the same timestamp
CREATE TABLE t1 AS
SELECT * FROM user_tables
WHERE 1=2;
SELECT TABLE t2 AS
SELECT * FROM user_indexes
WHERE 1=2;
DECLARE
curTime TIMESTAMP := SYSTIMESTAMP;
BEGIN
INSERT INTO t1
SELECT *
FROM user_tables
AS OF TIMESTAMP curTime;
INSERT INTO t2
SELECT *
FROM user_indexes
AS OF TIMESTAMP curTime;
END;
/
SELECT COUNT(*)
FROM t1;
SELECT COUNT(*)
FROM t2;
AS OF Demo
AS OF Demo
CREATE TABLE t AS
SELECT *
FROM dba_objects_ae
WHERE 1=2;
SELECT COUNT(*)
FROM t;
SELECT current_scn
FROM v$database;
INSERT INTO t
SELECT *
FROM dba_objects_ae
WHERE rownum < 101;
COMMIT;
SELECT current_scn
FROM v$database;
INSERT INTO t
SELECT *
FROM dba_objects_ae
WHERE rownum < 1001;
COMMIT;
SELECT current_scn
FROM v$database;
INSERT INTO t
SELECT *
FROM dba_objects_ae
WHERE rownum < 10001;