| General Information |
| Subprograms |
|
| 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 |
Description |
| 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) |
|
| Dependencies |
| DBA_FLASHBACK_TXN_REPORT |
DBMS_XSTREAM_UTL_IVK |
| DBA_FLASHBACK_TXN_STATE |
KUPM$MCP |
| DBMS_CAPTURE_ADM_INTERNAL |
LOGMNR_DDL_TRIGGER_PROC |
| DBMS_CDC_EXPDP |
TIMESTAMP_TO_SCN |
| DBMS_CDC_UTILITY |
TRANSACTION_BACKOUT_REPORT$ |
| DBMS_LOGMNR_INTERNAL |
TRANSACTION_BACKOUT_STATE$ |
| 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 below |
| |
| 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 below |
| |
| 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 / 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 * 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 |
| |
| 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; |
