第6章 从用户错误恢复

第6章 从用户错误恢复
闪回技术概述
使用闪回丢弃
sql>flashback table t1 to before drop;
了解回收站
sql>select * from t1;
sql>drop table t1;
sql>show recyclebin;
sql>select object_name as recycle_name ,original_name,object_name from recyclebin;
sql>select * from "BIN$0ZVR8xxxxxxxxxxxxxxx"
sql>flashback table "BIN$0Zxxxxxxxxxxxxxxx" to before drop rename to t2;
sql>select * from t2;
sql>show recyclebin;
回收站和空间利用
sql>purge table "BIN$0ZVR8eDEQbK4sxxxxxxxxxxx==$0"
sql>purge tablespace users user test;
sql>purge recyclebin;
sql>connect / as sysdba;
sql>purge dba_recyclebin;

闪回丢弃和回收站的局限性
通过EM实现闪回丢弃表
使用闪回版本查询
1.
sql>update t1 set salary=18000 where employee='JONES';
sql>commit;
sql>update t1 set salary=21000 where employee='JONES';
sql>commit;
sql>update t1 set salary=25000 where employee='JONES';
sql>commit;
2.
sql>select salary from t1
version between
scn minvalue and maxvalue
where employee = 'JONES';
sql>select salary from t1
versions between timestamp
to_timestamp('2004-10-26 11:37:01','YYYY-MM-DD HH:MI:SS') and
to_timestamp('2004-10-26 11:43:01','YYYY-MM-DD HH:MI:SS')
where employee = 'JONES';
sql>select current_scn,scn_to_timestamp(current_scn) from v$database;
sql>select to_char(versions_starttime,'DD-MON HH:MI') "START DATE",
           to_char(versions_endtime,'DD-MON HH:MI') "END DATE",
           versions_xid,
           versions_operation,
           employee,
           salary
           from test.t1
           versions between scn
           minvalue and maxvalue
           where employee = 'JONES'

使用闪回事务查询
SQL>SELECT table_name,operation,undo_sql from flashback_transaction_query
where xid='020018001F030000';
sql>select table_name,operation,undo_sql from flashback_transaction_query
where start_timestamp >= to_timestamp('2004-10-26 06:45:00','YYYY-MM-DD HH:MI:SS')
and table_owner='TEST';

使用闪回表
1.
sql>alter table t1 enable row movement;
2.
sql>select current_scn from v$database;
3.
sql>update t1 set salary=50000 where employee = 'JONES';
sql>commit;
4.
sql>flashback table t1 to scn 771511;
5.
sql>select * from t1 where employee='JONES';
sql>flashback table table_name to scn 771551 enable triggers;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/235507/viewspace-555223/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/235507/viewspace-555223/