Oracle Flashback Query
Version 11.2.0.3

AS OF SCN
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 02-MAY-07 10.54.39.218000 AM -07:00

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

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604687 02-MAY-07 10.55.01.984000 AM -07:00

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

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604694 02-MAY-07 10.55.14.421000 AM -07:00

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

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604700 02-MAY-07 10.55.26.218000 AM -07:00

SELECT * FROM t;

conn / 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('02-MAY-07 10.55.20.000000');
 
AS OF TIMESTAMP
Flashback by Timestamp SELECT <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 02-MAY-07 10.54.39.218000 AM -07:00

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

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604687 02-MAY-07 10.55.01.984000 AM -07:00

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

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604694 02-MAY-07 10.55.14.421000 AM -07:00

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

COMMIT;

SELECT current_scn, SYSTIMESTAMP
FROM v$database;
-- 5604700 02-MAY-07 10.55.26.218000 AM -07:00

SELECT * FROM t;

conn / as sysdba

set linesize 121
col owner format a10

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('02-MAY-07 10.55.00.000000');

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('02-MAY-07 10.55.10.000000');

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('02-MAY-07 10.55.20.000000');

SELECT owner, table_name, tablespace_name
FROM uwclass.t
AS OF TIMESTAMP TO_TIMESTAMP('02-MAY-07 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

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