Oracle DBMS_FLASHBACK
Version 21c

General Information
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.
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
DBMS_CAPTURE_ADM_INTERNAL DBMS_STREAMS_RPC_INTERNAL TIMESTAMP_TO_SCN
DBMS_LOGMNR_INTERNAL DBMS_TRAN_LIB TRANSACTION_BACKOUT_REPORT$
DBMS_LOGREP_EXP DBMS_XSTREAM_UTL_IVK TRANSACTION_BACKOUT_STATE$
DBMS_STREAMS_ADM_IVK KUPM$MCP TXNAME_ARRAY
DBMS_STREAMS_DATAPUMP 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
Related Sysem Privileges FLASHBACK ANY TABLE
Security Model Owned by SYS with EXECUTE granted to MDSYS and the DBA role
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_all_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_all_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
Built-in Packages
DBMS_FLASHBACK_ARCHIVE
Flashback Archive
Flashback Database
Flashback Drop
Flashback Query
Flashback Table
Flashback Transaction Query
Flashback Version Query
Recyclebin
Table Flashback
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