Oracle Flashback Version Query
Version 18.3.0.1

Library Note Morgan's Library Page Header
For how many years have you been working with physical servers that are starving your database of the memory necessary to deploy important new performance features such as the Result Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and Full Database Caching? Too long? Contact me to learn how to improve all queries ... not just some queries.
 
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');
Flashback Version Query SELECT <columns>
FROM <schema_name.table_name>
VERSIONS BETWEEN TIMESTAMP <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 TIMESTAMP 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 TIMESTAMP MINVALUE AND MAXVALUE;

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

SELECT owner, object_name, object_type
FROM t
VERSIONS BETWEEN TIMESTAMP
SYSDATE-5/60 AND SYSDATE
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 12cR2
What's New In 18cR1

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