当数据被错误修改时,如何还原。通过undo回滚段,不用开启闪回功能,有如下几种办法:
oracle的两个函数scn_to_timestamp和timestamp_to_scn
SQL> select timestamp_to_scn(sysdate) from dual;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
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]
即可
该功能包含以下伪列
versions_startscn
versions_starttime
versions_endscn
versions_endtime
versions_xid
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'
/