oracle 闪回

1.行级闪回

--为了便于测试,等一分钟后执行下面操作
delete from test where tid = 1;
---------------------------------------------------------------------------------------------
--下面这个查询可以查看1分钟前的表数据状态(1440是系统默认值,可更改)
select * from test as of timestamp sysdate - 1/1440;
----------------------------------------------------------------------------------------------
--下面语句即用来恢复数据(当前时间的前1分钟的数据),where条件是指更新特定数据.
insert into test select * from test as of timestamp sysdate - 1/1440 where tid = 1;

flashback table tablename to timestamp xxx或

flashback table tablename to scn xxx

flashback table test to timestamp to_timestamp('2008-12-25 19:52:00','yyyy-mm-dd hh24:mi:ss');

2.表级闪回

show recyclebin为什么没有数据呢？
首先们需要明白一点，recyclebin是user_recyclebin的同义词，如此你当前的登陆用户是system此时运用show recyclebin是没有数据据的
如果同一对像多次删除怎么在recyclebin中识别？
dba_recyclebin中对每删除一个对像都会以BIN$进行命名，同时会有相应的dropscn、createtime、droptime可以跟据这些对像进行定位，然后进行恢复

SQL>drop table abc；

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME  OBJECT TYPE  DROP TIME

ABC      BIN$XXUGsbYvSqa8Mrd6GstP+g==$0 TABLE      2005-08-29:18:03:10

显示了被删除对象的原有名字，删除后的名字，对象类型以及删除时间。

通过使用flashback table语句就可以恢复表！

SQL> flashback table abc to before drop;

闪回完成。

3.库级闪回

SQL>flashback database to timestamp to_timestamp(xxx);
SQL>flashback database to scn xxx；

select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

shutdown immediate;
startup mount;

flashback database to scn 5540916;

alter database open resetlogs;

4.查看闪回

--查看当前scn
select current_scn from v$database;
-----------------------------------------------------------------------------------------------
--查看scn数据
select * from test as of scn 1163174;

select * from test as of timestamp sysdate-1/1440;
select * from test as of timestamp to_timestamp('2014-03-14 10:26:38','yyyy-mm-dd hh24:mi:ss');
select * from test as of timestamp sysdate-3/1440;
select * from test as of scn 1107246;

5.查看闪回版本
select versions_starttime,versions_endtime,versions_xid,versions_operation,ename
2* from tyger versions between timestamp to_timestamp('2014-03-14 14:41:46','yyyy-mm-dd hh24:mi:ss') and maxvalue order by 1

6.闪回事务查询
YS@ORCL>select undo_sql from flashback_transaction_query where xid='080016000F020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "TYGER"."TYGER" set "SAL" = '1057' where ROWID = 'AAANQ3AAGAAAAYMAAA';

SYS@ORCL>grant select any transaction to tyger;
Grant succeeded.
SYS@ORCL>conn tyger/tyger
Connected.
TYGER@ORCL>select undo_sql from flashback_transaction_query where xid='080016000F020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "TYGER"."TYGER" set "SAL" = '1057' where ROWID = 'AAANQ3AAGAAAAYMAAA'; //undo语句