Oracle DBMS_FLASHBACK
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Part of this package was developed before the 10g release of command-line Flashback capabilities ... and is now obsolete. TRANSACTION_BACKOUT is a new capability added in 11g and is priceless.
AUTHID CURRENT_USER
Constants for Transaction Backout
Name Data Type Value
nocascade BINARY_INTEGER 1
Default: Backs out specified transactions, expected to have no dependent transactions. If dependents an exception is raised and the error can be found in DBA_FLASHBACK_TXN_REPORT.
nocascade_force BINARY_INTEGER 2
Backs out specified transactions, ignoring dependent transactions. Backout happens in reverse order of commit times.
noconflict_only BINARY_INTEGER 3
Backs out changes to nonconflicting rows. The database remains consistent but atomicity is lost.
cascade BINARY_INTEGER 4
Backs out specified transactions and all dependent transactions in a post-order fashion (children first: parents second).
Data Types Used CREATE OR REPLACE TYPE "SYS"."TXNAME_ARRAY" AS VARRAY(100) OF VARCHAR2(256);
Dependencies
DBA_FLASHBACK_TXN_REPORT DBMS_STREAMS_ADM_IVK TIMESTAMP_TO_SCN
DBA_FLASHBACK_TXN_STATE DBMS_STREAMS_DATAPUMP TRANSACTION_BACKOUT_REPORT$
DBMS_CAPTURE_ADM_INTERNAL DBMS_STREAMS_RPC_INTERNAL TRANSACTION_BACKOUT_STATE$
DBMS_CDC_EXPDP DBMS_TRAN_LIB TXNAME_ARRAY
DBMS_CDC_UTILITY DBMS_XSTREAM_UTL_IVK WWV_FLOW_AUDIT
DBMS_LOGMNR_INTERNAL KUPM$MCP WWV_FLOW_GEN_API2
DBMS_LOGREP_EXP LOGMNR_DDL_TRIGGER_PROC XID_ARRAY
Documented Yes
Exceptions
Error Code Reason
ORA-08180 Time specified is too old
ORA-08181 Invalid system change number specified
ORA-08182 User cannot begin read-only or serializable transactions in Flashback mode
ORA-08183 User cannot enable Flashback within an uncommitted transaction
ORA-08184 User cannot enable Flashback within another Flashback session
ORA-08185 SYS cannot enable Flashback mode
First Available 9.0.1
Related Sysem Privileges FLASHBACK ANY TABLE
Security Model Owned by SYS with EXECUTE granted to the APEX_040200 and DBA roles
Source {ORACLE_HOME}/rdbms/admin/dbmstran.sql
Subprograms
 
DISABLE
End Flashback Mode dbms_flashback.disable;
See Demo At Page Bottom
 
ENABLE_AT_SYSTEM_CHANGE_NUMBER
Enable flashback mode as of a specific SCN dbms_flashback.enable_at_system_change_number(query_scn IN NUMBER);
See Demo At Page Bottom
 
ENABLE_AT_TIME
Enable Flashback Mode As Of A Point-In-Time dbms_flashback.enable_at_time(query_time IN TIMESTAMP);
See Demo At Page Bottom
 
GET_SYSTEM_CHANGE_NUMBER
Get the current SCN dbms_flashback.get_system_change_number RETURN NUMBER;
SELECT dbms_flashback.get_system_change_number
FROM dual;
 
TRANSACTION_BACKOUT
Transaction backout interface

Overload 1
dbms_flashback.transaction_backout(
numtxns NUMBER,
xids    xid_array,
options BINARY_INTEGER DEFAULT NOCASCADE,
scnhint NUMBER DEFAULT 0);
conn sys@pdbdev as sysdba

shutdown immediate;

startup mount;

alter database archivelog;
alter database open;
alter system archive log current;
alter database add supplemental log data;

conn uwclass/uwclass@pdbdev

CREATE TABLE t1 (
testcol VARCHAR2(3));

CREATE TABLE t2 (
testcol VARCHAR2(3));

CREATE OR REPLACE TRIGGER row_level
BEFORE INSERT
ON t1
FOR EACH ROW

BEGIN
  INSERT INTO t2
  VALUES
  (:NEW.testcol);
END row_level;
/

BEGIN
  INSERT INTO t1 VALUES ('ABC');
  INSERT INTO t1 VALUES ('DEF');
  COMMIT;
  user_lock.sleep(500);
  INSERT INTO t1 VALUES ('GHI');
  INSERT INTO t1 VALUES ('JKL');
  COMMIT;
  user_lock.sleep(500);
  INSERT INTO t1 VALUES ('MNO');
  COMMIT;
  user_lock.sleep(500);
END;
/

SELECT * FROM t1;
SELECT * FROM t2;

SELECT versions_xid, versions_startscn, versions_endscn, versions_operation, testcol
FROM t1
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

desc sys.xid_array

SELECT table_name
FROM user_tables;

set serveroutput on

-- choose the middle xid
DECLARE
 xa sys.xid_array := sys.xid_array();
BEGIN
  xa.extend;
  dbms_output.put_line(xa.last);
  xa(1) := '04000700A2020000';
  dbms_flashback.transaction_backout(1, xa);
END;
/

SELECT * FROM t1;
SELECT * FROM t2;

COMMIT;

SELECT table_name
FROM user_tables;

desc sys_ktftb_sql_table

SELECT * FROM sys_ktftb_sql_table;

desc sys_ktftb_key_table

SELECT * FROM sys_ktftb_key_table;
Overload 2 dbms_flashback.transaction_backout(
numtxns  NUMBER,
xids     IN xid_array,
options  IN BINARY_INTEGER DEFAULT NOCASCADE,
timehint IN TIMESTAMP);
TBD
Overload 3 dbms_flashback.transaction_backout(
numtxns NUMBER,
names   txname_array,
options BINARY_INTEGER DEFAULT NOCASCADE,
scnhint NUMBER DEFAULT 0);
TBD
Overload 4 dbms_flashback.transaction_backout(
numtxns  NUMBER,
names    txname_array,
options  BINARY_INTEGER DEFAULT NOCASCADE,
timehint TIMESTAMP);
TBD
 
Demos
DBMS_FLASHBACK by System Change Number conn uwclass/uwclass@pdbdev

SELECT COUNT(*)
FROM serv_inst;

CREATE TABLE sibak AS
SELECT *
FROM serv_inst;

VARIABLE scn_save NUMBER;
exec :scn_save := dbms_flashback.get_system_change_number;
print scn_save

SELECT COUNT(*)
FROM serv_inst;

DELETE FROM serv_inst;

COMMIT;

SELECT COUNT(*)
FROM serv_inst;

DECLARE
 TYPE si_array IS TABLE OF serv_inst%ROWTYPE;
 si_data si_array;

 CURSOR flash_cur IS
 SELECT *
 FROM serv_inst;

 flash_rec flash_cur%ROWTYPE;
BEGIN
  dbms_flashback.enable_at_system_change_number(:scn_save);
  OPEN flash_cur;
  dbms_flashback.disable;
  LOOP
    FETCH flash_cur BULK COLLECT INTO si_data LIMIT 250;

    FORALL i IN 1..si_data.COUNT
    INSERT INTO serv_inst VALUES si_data(i);

    EXIT WHEN flash_cur%NOTFOUND;
  END LOOP;
  COMMIT;
  CLOSE flash_cur;
END;
/
DBMS_FLASHBACK by Timestamp Recovery SELECT COUNT(*)
FROM SERVERS;

CREATE TABLE sbak AS
SELECT *
FROM servers;

DELETE FROM servers;

COMMIT;

SELECT COUNT(*)
FROM servers;

exec dbms_flashback.enable_at_time(SYSTIMESTAMP - 10/1440);

SELECT COUNT(*)
FROM servers;

SELECT *
FROM servers;

exec dbms_flashback.disable;

SELECT *
FROM servers;

INSERT INTO servers
SELECT *
FROM sbak;

COMMIT;

Related Topics
Built-in Functions
DBMS_FLASHBACK_ARCHIVE
Flashback Archive
Flashback Database
Flashback Drop
Flashback Query
Flashback Table
Flashback Transaction Query
Flashback Version Query
Packages
Recyclebin
Table Flashback

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-2014 Daniel A. Morgan All Rights Reserved