| 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 |
| |
| 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 |
Transaction backout interface
Overload 1
SR Opened on metalink 8/19/08 |
dbms_flashback.transaction_backout(
numtxns NUMBER,
xids xid_array,
options BINARY_INTEGER DEFAULT nocascade,
scnhint NUMBER DEFAULT 0); |
set linesize 121
col program format a40
SELECT sid, serial#, audsid, user#, schema#, program
FROM v$session
WHERE service_name = 'SYS$USERS';
CREATE TABLE t (
testcol VARCHAR2(3));
SELECT dbms_flashback.get_system_change_number FROM dual;
insert into t values ('ABC');
insert into t values ('DEF');
COMMIT;
insert into t values ('GHI');
insert into t values ('JKL');
COMMIT;
insert into t values ('MNO');
COMMIT;
SELECT dbms_flashback.get_system_change_number FROM dual;
SELECT versions_xid, versions_startscn, versions_endscn,
versions_operation, testcol
FROM t
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
desc sys.xid_array
DECLARE
xa sys.xid_array := sys.xid_array();
BEGIN
xa.extend;
dbms_output.put_line(xa.last);
xa(1) := '0A001D0046100000';
dbms_flashback.transaction_backout(1,
xa);
END;
/ |
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; |