flashback闪回分为三大类
第一，flashback table
第二，flashback database
第三，flashback query
(一)，开启关闭回收站
alter session setrecyclebin =on;
altersession set recyclebin= off;
altersystem set recyclebin=on;
altersystem set recyclebin=off;
1,查看回收站里的内容
show recyclebin
2,,情况回收站
purge recyclebin
3,从回收站闪回的时候
flashback table t1to before drop
flashback table"BIN$mKoYM02RCojgQAB/AQAvyw==$0" to before drop;
(二)flashback query 闪回
1,select* from emp as of timestamp sysdate-1/1440; 查询一分钟以前数据库的状态
查询完毕之后我们插入数据
insertinto emp select * from emp as of timestamp sysdate-1/1440
2,用scn的方式闪回
取得系统当前的scn
(1)9i的时候我们取得scn比较麻烦
selectdbms_flashback.get_system_change_number from dual;
(2)10g之后我们可以直接从视图里查询到
select current_scnfrom v$database;
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
534578
3,可以scn跟timestamp互换
SQL> select scn_to_timestamp(534578)from dual;
SCN_TO_TIMESTAMP(534578)
---------------------------------------------------------------------------
06-SEP-12 11.05.52.000000000 AM
SQL> select timestamp_to_scn('06-SEP-1211.05.52.000000000') from dual;
TIMESTAMP_TO_SCN('06-SEP-1211.05.52.000000000')
-----------------------------------------------
534574
scn闪回查询
select * from emp as of scn 534574
4,两个scn之间的交易 闪回事物查询
select xid,commit_scn,commit_timestamp,operation,undo_sqlfrom flashback_transaction_querywhere table_name='EMP' and table_owner='SCOTT' and commit_scn>=534574 andcommit_scn<=534624
(1)闪回版本查询
SQL>!date "+%F %T"
2012-09-0611:37:13
SQL>colversions_starttime for a25
SQL>colversions_endtime for a25
SQL> select versions_starttime,versions_endtime, versions_xid, versions_operation,ename,sal
fromscott.emp versions between timestamp to_timestamp('2012-09-06 11:37:13','YYYY-MM-DD HH24:MI:SS')
andmaxvalue order by VERSIONS_STARTTIME ;
做交易在查询
(三)flashback database
1,,激活数据库闪回
alter databaseflashback on ----在mount状态下
showparameter db_recover
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_recovery_file_dest string /u01/tiger/flash_recovery_area
db_recovery_file_dest_size big integer 2G
mount状态下闪回数据库
flashback database to scn 534574
flashbackdatabase to timestamp sysdate-10/1440
创建存储点
create restore point aa;
select * from v$restore_point 查看存储点对应的scn
flashback database restore point aa; mount状态下恢复
drop restore point aa; 删除存储点