工作日志：闪回查询及scn_to_timestamp

原创 eygle 2009-11-11

574

上周在培训期间，一个客户的数据库出了误UPDATE的问题，修改错了数据，请求我帮忙恢复。

闪回查询在这类误操作中非常有用，只要发现的及时，数据可以快速的闪回回来，我想这是开发人员也应该掌握的SQL查询。

记录一下工作日志：

SQL> desc lm_mtrp_syn_channelinfo
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
CHANNELID                                          NUMBER
SGWID                                              VARCHAR2(50)
SERVICETYPE                                        VARCHAR2(50)
SPNUMBER                                           VARCHAR2(50)
CMD                                                VARCHAR2(50)
CREATETIME                                         DATE
MORE                                               VARCHAR2(500)

SQL> select count(*) from lm_mtrp_syn_channelinfo;

COUNT(*)
----------
        21

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
               708677858

SQL> select count(*) from lm_mtrp_syn_channelinfo as of scn 708600000;

COUNT(*)
----------
        21

SQL> select count(*) from lm_mtrp_syn_channelinfo as of scn 708000000;

COUNT(*)
----------
        21

SQL> select scn_to_timestamp(708000000) from dual;

SCN_TO_TIMESTAMP(708000000)
---------------------------------------------------------------------------
05-NOV-09 01.37.08.000000000 PM

SQL> create table lm_temp as select * from lm_mtrp_syn_channelinfo as of scn 708000000;

Table created.

-The End-

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

工作日志：闪回查询及scn_to_timestamp

评论