闪回查询，版本查询

当数据被错误修改时，如何还原。通过undo回滚段，不用开启闪回功能，有如下几种办法：

 

oracle的两个函数scn_to_timestamp和timestamp_to_scn

SQL> select timestamp_to_scn(sysdate) from dual;

TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
                  3289230

SQL> select to_char(scn_to_timestamp(3289211),'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SCN_TO_TIMESTAMP(3289211),'YYY
--------------------------------------
2012-02-29 04:06:14

 

附上修改session显示格式的语句

alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss';

 

一．利用回滚段信息闪回查询，和对表进行闪回

 

查看当前时间与scn号对应的关系

 

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now ,to_char(dbms_flashback.get_system_change_number) scn from dual;

 

NOW

--------------------------------------

SCN

--------------------------------------------------------------------------------

2011-11-24 13:59:45

1186296

 

通过timestamp闪回查询数据

select * from e as of timestamp to_timestamp('2011-11-24 12:10:00','yyyy-mm-dd hh24:mi:ss');

 

通过scn闪回查询数据

SQL> select * from e as of scn 1186000;

  

将表中的数据闪回到某一时刻

先开启行移动

SQL> alter table e enable row movement;

Table altered.

 

再进行表的闪回

SQL> flashback table e to scn 1186800;

Flashback complete.

 

或

 

SQL> flashback table e to timestamp to_timestamp('2011-11-24 12:10:00','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

 

如执行时报没有权限则授予此功能需要的权限

ant execute on dbms_flashback to 用户名;

 

二．Versions between版本检查

可以查看指定时间段内undo表空间（默认保存900秒）中记录的不同版本（只含被提交的）

注：保存时间可通过show parameter undo_retention查看，单位为秒

 

在标准查询后添加

versions between timestamp[或scn]  时间1   and  时间2 

即可

 

该功能包含以下伪列

versions_startscn           开始scn号

versions_starttime          开始时间

versions_endscn            结束scn号

versions_endtime           结束时间

versions_xid                 该操作的事务ID

versions_operation         执行的操作

 

范例：

 

select ename,sal,versions_startscn,versions_endscn,versions_operation, versions_xid from e

versions between timestamp to_timestamp('2011-11-24 14:15:00','yyyy-mm-dd hh24-mi-ss')

and to_timestamp('2011-11-24 14:20:19','yyyy-mm-dd hh24-mi-ss')

 

或

 

select ename,sal,versions_startscn,versions_endscn,versions_operation, versions_xid from e

versions between scn 1187395 and 1187538

 

三．查看回滚段的回滚语句(可以根据上面的方法查到的XID号，查找还原语句)

 

select undo_sql,to_char(start_timestamp,'yyyy-mm-dd hh:mi:ss') starttime,

to_char(commit_timestamp,'yyyy-mm-dd hh:mi:ss') committime

from flashback_transaction_query

where table_name='E'

and XID= '03000400CB020000'

/