Flashback Transaction in Oracle 11g

Flashback transaction 可以回退已经提交的事务,并且可以选择是否回退和该事务有关联的数据

启用flashback transaction需要开启数据库的supplemental log(minimal 和 primary)

Sqlplus / as sysdba

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

SQL>ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 

创建测试表

CONN test /test

CREATE TABLE test_tab (

id NUMBER,

description VARCHAR2(50),

CONSTRAINT test_tab_pk PRIMARY KEY (id)

);

 

INSERT INTO test_tab (id, description) VALUES (1, ‘One’);

COMMIT;

INSERT INTO test_tab (id, description) VALUES (2, ‘Two’);

COMMIT;

UPDATE test_tab SET description = ‘One2Three’ WHERE id = 1;

COMMIT;

UPDATE test_tab SET description = ‘Tow2Four’ WHERE id = 2;

COMMIT;

DELETE FROM test_tab WHERE id = 1;

COMMIT;

 

select * from test_tab

    ID DESCRIPTION

———- ——————–

     2 Field


 

登录em

在schema->tables选择需要表 菜单选择flashback transaction

选择需要flashback的transaction

其中有个recovery option需要注意:

Nonconflict Only: Only non-conflicting rows of the target transaction will be backed out. (只flashback没有冲突的行)

Nocascade Force:The target transaction will be forcibly backed out without considering dependent transactions.(不考虑冲突的情况把所有的该事务的全部flashback)

Cascade:The target transaction and all dependent transactions will be backed out.(把所有约束关系的行全部回退包含其他有关系的事务中修改的行)

 

参考测试的例子中:

如果我们flashback的事务是:UPDATE test_tab SET description = ‘One2Three’ WHERE id = 1;

那么参数设为:

Nonconflict Only :就没有可以flashback的纪录

    Nocascade Force:可以看到flashback的SQL语句是:update “TEST”.”T1″ set “DESCRIPTION” = ‘One’ where “ID” = ’1′ and “DESCRIPTION” = ‘One2Three’;(由于

             ID=1的记录已经被删掉了,所有这个SQL执行是0 rows updated)

    Cascade:可以看到flashback的SQL语句是:(insert into “TEST”.”T1″(“ID”,”DESCRIPTION”) values (’1′,’One2Three’);

update “TEST”.”T1″ set “DESCRIPTION” = ‘One’ where “ID” = ’1′ and “DESCRIPTION” = ‘One2Three’;

)

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>