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.
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;
