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 Version Query

Version 11.1
 
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;  -- otherwise never do incremental commits
END;
/

set linesize 121

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_name = 'DUAL'
AND object_type = 'TABLE';

COMMIT;

UPDATE t
SET object_type = 'PROCEDURE'
WHERE object_name = 'DUAL'
AND object_type = 'VIEW';

COMMIT;

UPDATE t
SET object_type = 'FUNCTION'
WHERE object_name = 'DUAL'
AND object_type = 'PROCEDURE';

COMMIT;

DELETE FROM t
WHERE object_name = 'DUAL'
AND object_type = 'PROCEDURE';

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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [79 users online]    © 2010 psoug.org