Oracle Flashback Query
Version 21c

Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
 
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;

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;

Related Topics
Flashback Archive
Flashback Database
Flashback Drop
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
Pseudocolumns
Recycle Bin
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx