CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Flashback Query

Version 11.1
 
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');
 
AS OF Demo

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;
 
Related Topics
Flashback Archive
Flashback Database
Flashback Drop
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Flashback Version Query
Pseudocolumns
Recycle Bin
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [64 users online]    © 2010 psoug.org