[Oracle]Flashback闪回机制(二)
1 where id=1;
A105024@O02DMS1>commit;
开始第二个事务：
A105024@O02DMS1>insert into test values (2);
A105024@O02DMS1>update test set id=22 where id=2;
A105024@O02DMS1>commit;
查看flashback_transaction_query视图获得事务的演变历史：
A105024@O02DMS1>select XID,OPERATION,COMMIT_SCN,UNDO_SQL from flashback_transaction_query where xid in
2 (select versions_xid from test versions between scn minvalue and maxvalue);
XID OPERATION COMMIT_SCN UNDO_SQL
---------------- ---------- ---------- ------------------------------------------------------------
10000D000C720000 UPDATE 6629456273 update "A105024"."TEST" set "ID" = '2' where ROWID = 'AACsnzAAEAAABSnAAB';
10000D000C720000 INSERT 6629456273 delete from "A105024"."TEST" where ROWID = 'AACsnzAAEAAABSnAAB';
10000D000C720000 BEGIN 6629456273
0C0002002A720000 UPDATE 6629456260 update "A105024"."TEST" set "ID" = '1' where ROWID = 'AACsnzAAEAAABSnAAA';
0C0002002A720000 INSERT 6629456260 delete from "A105024"."TEST" where ROWID = 'AACsnzAAEAAABSnAAA';
0C0002002A720000 BEGIN 6629456260
4. Flashback Table
根据Flashback Query的演变历史，就可以确定需要回退的时间点，然后再利用Flashback Table功能真正实现回退，注意：在真正回退之前，必须启用row movement。
Flashback Drop
Flashback drop是从
Oracle 10g开始才有的功能，原理是每个表空间都会有严格回收站的逻辑区域，当drop时，被删除的表及其关联对象不会被物理删除，只是转移到回收站中，给用户提供一个恢复的可能。
使用Flashback drop需要注意以下几点：
1. 对system表空间无效
2. sqlplus的版本不能低于10g，否则很多命令无法使用
下面做个测试：
SQL> create table test as select * from dba_objects;
SQL> drop table test;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$S5L+aNpzQmOScn8VfpJBAA==$0 TABLE 2011-12-15:22:38:30
SQL> create table test as select * from dba_objects where 2=1;
SQL> drop table test;
SQL> show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST BIN$XHwrz1OaQaSeq/NQIE85hw==$0 TABLE 2011-12-15:22:39:34
TEST BIN$S5L+aNpzQmOScn8VfpJBAA==$0 TABLE 2011-12-15:22:38:30
此时recyclebin里有两个test表，查看中两个表的数据个数：
SQL> select count(*) from "BIN$XHwrz1OaQaSeq/NQIE85hw==$0";
COUNT(*)
----------
0
SQL> select count(*) from "BIN$S5L+aNpzQmOScn8VfpJBAA==$0";
COUNT(*)
----------
1000000
假设想要恢复后一张表：
SQL> flashback table "BIN$S5L+aNpzQmOScn8VfpJBAA==$0" to before drop;
Flashback complete.
SQL> select count(*) from test;
COUNT(*)
----------
1000000
假设现在还要恢复前一张表，并重新命名为test2：
SQL> flashback table "BIN$XHwrz1OaQaSeq/NQIE85hw==$0" to before drop rename to t
est2;
Flashback complete.
SQL> select count(*) from test2;
COUNT(*)
----------
0
Flashback Database
1. 配置Flashback Database
1）Flashback 功能默认是关闭的：
SQL> select name,flashback_on from v$database;
NAME FLASHBACK_ON
--------- ------------------
O01DMS0 NO
2）配置Flash recovery area:
SQL> alter system set db_recovery_file_dest_size=2G scope=both;
SQL> alter system set db_recovery_file_dest='H:\flashback' scope=both;