Oracle Flashback Version Query
Version 21c

Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
 
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 19c
What's New In 20c-21c

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
  DBSecWorx