oracle11G闪回flashback - 数据库编程

ck_transaction_query中查询引起数据变化的

事务，和撤销事务的SQL语句，就是查询 operation和undo_sql 列。

SQL> select xid,start_timestamp,operation,undo_sql from flashback_transaction_query where table_name='STUDENT'; XID START_TIMESTAMP OPERATION UNDO_SQL ---------------- --------------- -------------------------------- -------------------------------------------------------------------------------- 0F00020002010000 24-1月-15 20:32: UNKNOWN 0F00040002010000 24-1月-15 20:32: UNKNOWN 0F00040002010000 24-1月-15 20:32: UNKNOWN 13000100FE000000 24-1月-15 19:52: UNKNOWN 1400200004010000 24-1月-15 20:32: UNKNOWN

如上图所示的，但是这里查不出来奇怪了。。。。通过undo_sql来进行回滚事务（就是再做次反向操作）

oracle10g的闪回表：

Oracle10g的闪回表是把表里的数据回退到以前的某个时

刻或者SCN上。

特点：可以在线操作；自动恢复相关的属性，包括索引、

触发器等。

前提：对表启用行迁移。

语法：flashback table to timestamp | scn

Connected as hr@JIAGULUN
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 3 zhangsan 21
SQL> alter table student enable row movement; Table altered
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3143334
SQL> delete from student where id in(1); 0 rows deleted
SQL> delete from student where id in(3); 1 row deleted
SQL> commit; Commit complete
SQL> flashback table student to scn 3143334; Done
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 3 zhangsan 21
SQL>

注意：sys的表不能闪回。

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as sys@JIAGULUN AS SYSDBA
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 1 zhangsan 20 2 lisi 21 3 wangwu 22
SQL> alter table student enable row movement; Table altered
SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------ 3142994
SQL> delete from student where id in(1,2); 2 rows deleted
SQL> commit; Commit complete
SQL> select * from student; ID NAME AGE ----------- -------------------- ----------- 3 wangwu 22
SQL> flashback table student to scn 3142994; flashback table student to scn 3142994 ORA-08185: 用户 SYS 不支持闪回
SQL> show user; User is "SYS"
SQL>

oracle10g的闪回删除：

Oracle10g的闪回删除：可以恢复一个被drop的对象，因

为进行drop时，Oracle先把它放到回收站中。（回收站和操作系统的回收站很相似）

回收站内的信息：show recyclebin

闪回删除：flashback table to before drop;

彻底删除：drop table purge;

清空回收站: purge recyclebin;
?

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as hr@JIAGULUN
SQL> purge recyclebin; Done
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- ADDRESS TABLE
SQL> select * from address; ID NAME ----------- -------------------- 1 jiangxi 2 hunan
SQL> show recyclebin; SQL> select * from user_recyclebin;--回收站并没有任何垃圾 OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE ------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ---------- -------------------------------- ---------- --------- ---------- ----------- ------------ ----------
SQL> drop table address;--删除表 Table dropped
SQL> show recyclebin; SQL> select * from user_recyclebin;--会发现回收站中出现了——表，索引垃圾 OBJECT_NAME ORIGINAL_NAME OPE

oracle11G闪回flashback(四)