oracle deleted,oracle之DELETE后的恢复

由于开发人员在早晨大概在9点左右不小心删除了某张表的一些数据(delete from logmessage where where taskid='6471';)，目前急需恢复删除的数据，恢复步骤如下

第一：首先通过大概时间点来查询SCN(将删除时间转换为scn)

SQL> select timestamp_to_scn(to_timestamp('2012-02-13 09:00:00','YYYY-MM-DD HH:MI:SS')) from dual;

TIMESTAMP_TO_SCN(TO_TIMESTAMP(

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

16360271

第二：通过SCN查询被删除的数据(将logmessage表中的scn点的数据取出,然后可以根据这个数据进行还原操作)

SQL> select * from logmessage as of scn 16360271 where taskid='6471';

LOGID                TASKID     LOGNAME                                                                          LOGTIME     TESTNET              TESTTYPE                                           TASKTYPE                                           TESTREGION                                                                       REGIONID             LOGDATAQUAN          EQUIPMENTNAME        SAVEPATH                                                                         ISDEL      UPDATENAME                                                                       UPUSER                                             TESTTIME    TASKNAME                                                                         TESTTIMELONG TESTDATAID

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

10542                6471       20110314_中国_3网测试_市区测试与评估_市区mos及do上传_0314-142146-2.loc           2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    23920640             ZTE AC8710           #Path/6471\20110314_中国_3网测试_市区测试与评估_市区mos及do上传_0314-142146-2.loc            20110314_中国_3网测试_市区测试与评估_市区mos及do上传_0314-142146-2.loc           924                                                2011-3-14   2月10日数据测试数据一                                                                7756.203 17043

10543                6471       20110314_中国_3网测试_市区测试与评估_市区mos及do上传_v1_0314-092232-2.loc        2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    22413311             ZTE AC8710           #Path/6471\20110314_中国_3网测试_市区测试与评估_市区mos及do上传_v1_0314-092232-2.loc            20110314_中国_3网测试_市区测试与评估_市区mos及do上传_v1_0314-092232-2.loc        924                                                2011-8-10   2月10日数据测试数据一                                                                 6650.39 17044

10544                6471       20110314_中国_3网测试_市区测试与评估_市区数据下载_v1_0314-092029-4.loc           2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    16646143             ZTE AC8710           #Path/6471\20110314_中国_3网测试_市区测试与评估_市区数据下载_v1_0314-092029-4.loc            20110314_中国_3网测试_市区测试与评估_市区数据下载_v1_0314-092029-4.loc           924                                                2011-8-10   2月10日数据测试数据一                                                                6751.609 17045

10545                6471       20110314_中国_3网测试_市区测试与评估_市区数据下载_v1_0314-142703-4.loc           2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    19054591             ZTE AC8710           #Path/6471\20110314_中国_3网测试_市区测试与评估_市区数据下载_v1_0314-142703-4.loc            20110314_中国_3网测试_市区测试与评估_市区数据下载_v1_0314-142703-4.loc           924                                                2011-8-10   2月10日数据测试数据一                                                                7338.906 17046

10546                6471       20110318_中国_3网测试_市区测试与评估_市区仓山mos及do上传_v1_0318-143629-2.loc    2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    4702207              ZTE AC8710           #Path/6471\20110318_中国_3网测试_市区测试与评估_市区仓山mos及do上传_v1_0318-143629-2.loc            20110318_中国_3网测试_市区测试与评估_市区仓山mos及do上传_v1_0318-143629-2.loc    924                                                2011-3-21   2月10日数据测试数据一                                                                2003.719 17047

10547                6471       20110321_中国_3网测试_市区测试与评估_市区数据下载_v1_0321-143424-4.loc           2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    21626879             ZTE AC8710           #Path/6471\20110321_中国_3网测试_市区测试与评估_市区数据下载_v1_0321-143424-4.loc            20110321_中国_3网测试_市区测试与评估_市区数据下载_v1_0321-143424-4.loc           924                                                2011-8-10   2月10日数据测试数据一                                                                6863.657 17048

10548                6471       20110322_中国_3网测试_市区测试与评估_市区mos及do上传_v1_0322-090019-2.loc        2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    22724607             ZTE AC8710           #Path/6471\20110322_中国_3网测试_市区测试与评估_市区mos及do上传_v1_0322-090019-2.loc            20110322_中国_3网测试_市区测试与评估_市区mos及do上传_v1_0322-090019-2.loc        924                                                2011-3-21   2月10日数据测试数据一                                                                8096.219 17049

10549                6471       20110322_中国_3网测试_市区测试与评估_市区数据下载_v1_0322-090735-4.loc           2012-2-10 1 电信CDMA             语音                                               自动路测                                           市区                                                                             2                    28060278             ZTE AC8710           #Path/6471\20110322_中国_3网测试_市区测试与评估_市区数据下载_v1_0322-090735-4.loc            20110322_中国_3网测试_市区测试与评估_市区数据下载_v1_0322-090735-4.loc           924                                                2011-8-10   2月10日数据测试数据一                                                                8039.563 17050

8 rows selected

第三：确认查询被删除后表中总的有多少数据

SQL> select count(*) from logmessage;

COUNT(*)

----------

2515

SQL> select * from logmessage where where taskid='6471';这个查询到并没有taskid='6471'的值在logmessage表中

第四：通过SCN的值来恢复被删除的数据(taskid='6471')

SQL> insert into logmessage  select * from logmessage as of scn 16360271 where taskid='6471';

8 rows inserted

SQL> commit;

Commit complete

第五：确认被恢复的数据

SQL> select count(*) from logmessage;

COUNT(*)

----------

2523