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

Version 11.1
 
Basic Functionality Demo

Demo using the flashback_transaction_query view
 
conn uwclass/uwclass

create table t (
mycol VARCHAR2(20));

INSERT INTO t VALUES ('ABC');

COMMIT;

INSERT INTO t VALUES ('DEF');

COMMIT;

INSERT INTO t VALUES ('GHI');

COMMIT;

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, mycol
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

conn / as sysdba

desc flashback_transaction_query

set linesize 121
col operation format a10
col undo_sql format a65

-- choose the 'DEF' entry's xid
SELECT operation, undo_sql, logon_user
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0A0009005F060000');

delete from "UWCLASS"."T" where ROWID = 'AAAPHYAAGAAABgqAAB';

COMMIT;

SELECT * FROM uwclass. t;

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, mycol
FROM uwclass.t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

UPDATE uwclass.t
SET mycol = 'MNO'
WHERE mycol = 'GHI';

INSERT INTO uwclass.t VALUES ('XYZ');

COMMIT;

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, mycol
FROM uwclass.t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
 
Related Topics
Flashback Archive
Flashback Database
Flashback Drop
Flashback Query
Flashback Table
Flashback Transaction Backout
Flashback Version Query
Pseudocolumns
Recycle Bin
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [50 users online]    © 2010 psoug.org