Oracle Flashback Version Query
Version 12.1.0.2

Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
 
Basic Functionality
Flashback Version Query SELECT <columns>
FROM <schema_name.table_name>
VERSIONS BETWEEN SCN <minimum_scn> AND <maximum_scn>
[WHERE <column_filter>]
[GROUP BY <non-aggregated_columns>]
[HAVING <group filter>
[ORDER BY <position_numbers_or_column_names>]
conn uwclass/uwclass@pdbdev

CREATE TABLE t AS
SELECT owner, object_name, object_type
FROM all_objects
WHERE 1=2;

desc t

DECLARE
 CURSOR fvq_cur IS
 SELECT owner, object_name, object_type
 FROM all_objects
 WHERE rownum < 11;
BEGIN
  FOR r IN fvq_cur LOOP
    INSERT INTO t
    VALUES r;
    COMMIT;  -- this is a bad practice done here intentionally
  END LOOP;  -- do not code incremental commits
END;
/

set linesize 141
col owner format a6

SELECT * FROM t;

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

UPDATE t
SET object_type = 'VIEW'
WHERE object_type <> 'VIEW'
AND rownum = 1;

COMMIT;

UPDATE t
SET object_type = 'PROCEDURE'
WHERE object_type <> 'VIEW'
AND rownum = 1;

COMMIT;

UPDATE t
SET object_type = 'FUNCTION'
WHERE object_type NOT IN ('VIEW','PROCEDURE')
AND rownum = 1;

COMMIT;

DELETE FROM t
WHERE object_type = 'PROCEDURE'
AND rownum = 1;

COMMIT;

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, rowid, owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE (rowid = 'AAAPHdAAGAAABgqAAA'
OR object_type = 'FUNCTION');

SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN SCN 5610589 AND 5610595
WHERE (rowid = 'AAAPHdAAGAAABgqAAA'
OR object_type = 'FUNCTION');

Related Topics
Flashback Archive
Flashback Database
Flashback Drop
Flashback Query
Flashback Table
Flashback Transaction Backout
Flashback Transaction Query
Pseudocolumns
Recycle Bin
What's New In 12cR1
What's New In 12cR2

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