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