eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« PowerPoint不能输入中文 - 令人抓狂的诡异 | Blog首页 | 10.2.0.4 LGWR Trace Warning: Log Write Time »

工作日志:闪回查询及scn_to_timestamp

上周在培训期间,一个客户的数据库出了误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-


历史上的今天...
    >> 2008-11-11文章:
    >> 2007-11-11文章:
    >> 2005-11-11文章:
           被人晃点

无觅

By eygle on 2009-11-11 14:28 | Comments (3) | FAQ | 2451 |

3 Comments

有相反的timestamp_to_scn不?

有的,看到了。
不过如果知道了确切的时间直接闪回到需要的时间也可以收到一样的效果啊?
没有体会到他的用途。。。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com