Oracle Flashback Version Query
Version 11.2.0.3

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

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

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