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 DBMS_FLASHBACK

Version 11.1
 
General
Note: Use of 10g Flashback capabilities is superior in all respects. This page has been updated for backward compatibility purposes.
Source {ORACLE_HOME}/rdbms/admin/dbmstran.sql
First Available 9.0.1
Constants (Transaction Backout)
Name Data Type Value
nocascade BINARY_INTEGER 1
nocascade_force BINARY_INTEGER 2
noconflict_only BINARY_INTEGER 3
cascade BINARY_INTEGER 4
Dependencies
DBMS_CDC_EXPDP DBMS_TRAN_LIB
DBMS_CDC_UTILITY KUPM$MCP
DBMS_LOGMNR_INTERNAL TIMESTAMP_TO_SCN
DBMS_LOGREP_EXP TXNAME_ARRAY
DBMS_STREAMS_ADM WWV_FLOW_AUDIT
DBMS_STREAMS_DATAPUMP WWV_FLOW_GEN_API2
DBMS_STREAMS_RPC_INTERNAL XID_ARRAY
System Privileges flashback any table
Security Model Execute is granted to the DBA role
 
DISABLE
End Flashback Mode dbms_flashback.disable;
see demo
 
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
 
ENABLE_AT_TIME
Enable Flashback Mode As Of A Point-In-Time dbms_flashback.enable_at_time(query_time IN TIMESTAMP);
see demo
 
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 (new 11g)

Transaction backout interface


Overload 1
dbms_flashback.transaction_backout(
numtxns NUMBER,
xids    xid_array,
options BINARY_INTEGER DEFAULT NOCASCADE,
scnhint NUMBER DEFAULT 0);
conn / 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

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 INT
O 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 versions_xid, versions_startscn, versions_endscn, versions_operation, testcol
FROM t1
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;


desc sys.xid_array

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 t;

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
 
Demo: Flashback To System Change Number

DBMS_FLASHBACK by System Change Number
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;

The Recovery Procedure
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 100;

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

    EXIT WHEN flash_cur%NOTFOUND;
  END LOOP;
  CLOSE flash_cur;
  COMMIT;
END;
/
 
Demo: Flashback To Point-in-Time

DBMS_FLASHBACK by time (this demo flashes back ten minutes)
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
Recyclebin
Table Flashback
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [189 users online]    © 2010 psoug.org