oracle数据误删除，更新时恢复数据

引用：http://book.51cto.com/art/200912/170984.htm

11.1.1  应用Flashback Query查询过去的数据

Flashback Query这一特性，最常被应用的就是修复误操作的数据了。注意，这并不是说Flashback Query能够恢复数据。Flashback Query本身不会恢复任何操作或修改，也不能告诉你做过什么操作或修改，实际上Flashback Query特性实际应用时，是基于标准SELECT的扩展，借助该特性能够让用户查询到指定时间点的表中的记录，相当于拥有了看到过去的能力，至于恢复，SELECT的结果都出来了，难道还不懂如何执行INSERT TABLE SELECT或CREATE TABLE AS SELECT吗？

11.1.1.1  基于时间的查询（AS OF TIMESTAMP）

以前面创建的FLASH_TBL表为例，先来删除几条记录并提交：

 
 
JSSPRE> DELETE FLASH_TBL WHERE ID<10;  
9 rows deleted.  
JSSPRE> COMMIT;  
Commit complete.  
JSSPRE> SELECT * FROM FLASH_TBL;  
    ID   VL  
---------- --  
    10   K  
    11   L  
    12   M  
    13   N  
    14   O  
    15   P  
    16   Q  
    17   R  
    18   S  
    19   T  
    20   U  
11   rows selected. 

这个时候FLASH_TB1表中ID<10的记录均已被删除，假设过了一会儿用户发现删除操作执行有误，仍需找回那些被误删的记录该怎么办呢？通过备份恢复吗，如果是在8i或之前版本，恐怕是需要这样，自9i之后，使用Flashback Query的特性，我们可以很轻松地恢复记录（注意并不是任何情况下都可以恢复哟，后面会讲到制约Flashback Query的一些因素，这里假设的都是在理想条件下）。

现在就演示应用Flashback Query，首先是找到它，假设当前距离删除数据有5分钟左右的时间，执行SELECT查询语句，并附加AS OF子句，例如：

 
 
JSSPRE> SELECT * FROM FLASH_TBL AS OF TIMESTAMP SYSDATE-5/1440;  
    ID  VL  
---------- --  
    1   A  
    2   B  
    3   C  
    4   D  
    5   E  
    6   F  
    7   G  
    8   H  
    9   I  
    10  K  
    11  L  
    12  M  
    13  N  
    14  O  
    15  P  
    16  Q  
    17  R  
    18  S  
    19  T  
    20  U  
    20      rows selected.  
JSSPRE> 

提 示

SYSDATE-5/1440是啥意思，1440又是怎么来的？

首先60（分）×24=1440，这样就计算出一天拥有多少分钟，SYSDATE是系统函数，用来取得当前的系统时间（以天为单位），SYSDATE-5/1440，得出的就是距当前时间5分钟前的记录了。后面示例中需要计算之前的某个时段时，均是使用这一方法。

你看，我们通过增加AS OF TIMESTAMP的语法，查询到的数据就是5分钟之前的，基于这一结果，可以轻易并且快速地将记录恢复：

 
 
JSSPRE> INSERT INTO FLASH_TBL  
  2  SELECT * FROM FLASH_TBL AS OF TIMESTAMP SYSDATE-5/1440  
  3  WHERE ID<10;  
9 rows created.  
JSSPRE> COMMIT;  
Commit complete. 

成功插入9条记录，查询表中当前的记录：

 
 
JSSPRE> SELECT * FROM FLASH_TBL;  
 
    ID  VL  
---------- --  
    10  K  
    11  L  
    12  M  
    13  N  
    14  O  
    15  P  
    16  Q  
    17  R  
    18  S  
    19  T  
    20  U  
    1   A  
    2   B  
    3   C  
    4   D  
    5   E  
    6   F  
    7   G  
    8   H  
    9   I  
20 rows selected. 

数据已被成功恢复。

如上述示例中所示，AS OF TIMESTAMP方式的使用非常方便，但是在某些情况下，我们建议使用AS OF SCN的方式执行Flashback Query。如需要对多个相互有主外键约束的表进行恢复时，如果使用AS OF TIMESTAMP的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，通过AS OF SCN方式则能够确保记录处理的时间点一致。

11.1.1.2  基于SCN的查询（AS OF SCN）

仍以前文中创建的表为例，既然是基于SCN的查询，我们首先就需要得到SCN，这里我们通过DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER函数来获取Oracle当前的SCN，之后再执行数据的修改操作

提 示

如何获取Oracle数据库当前的SCN？

获取当前SCN的方式非常多，除了使用DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER函数外，也可以通过查询V$DATABASE视图中的CURRENT_SCN列获取。不过，不管是通过查询视图，或是通过过程获取，操作的用户都必须拥有要操作对象的访问权限。

例如，授予用户使用DBMS_FLASHBACK包的权限：

 
 
JSSPRE> CONN/AS SYSDBA  
Connected.  
JSSPRE> GRANT EXECUTE ON DBMS_FLASHBACK TO SCOTT;  
Grant succeeded. 

又如，授予用户查询V$DATABASE视图的权限：

 
 
JSSPRE> CONN/AS SYSDBA  
Connected.  
JSSPRE> GRANT SELECT ON V_$DATABASE TO SCOTT;  
Grant succeeded. 

使用DBMS_FLASHBACK包获取当前的SCN，然后执行删除操作并提交：

 
 
JSSPRE> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;  
GET_SYSTEM_CHANGE_NUMBER  
------------------------  
                 1257245  
JSSPRE> DELETE FLASH_TBL WHERE ID>10;  
10 rows deleted.  
JSSPRE> COMMIT;  
Commit complete. 

执行SELECT语句并附加AS OF SCN子句，同时指定删除前的SCN，就可以查询到指定SCN时对象中的记录：

 
 
JSSPRE> SELECT * FROM FLASH_TBL AS OF SCN 1257245;  
    ID  VL  
---------- --  
    10  K  
    11  L  
    12  M  
    13  N  
    14  O  
    15  P  
    16  Q  
    17  R  
    18  S  
    19  T  
    20  U  
    1   A  
    2   B  
    3   C  
    4   D  
    5   E  
    6   F  
    7   G  
    8   H  
    9   I  
20 rows selected. 

执行INSERT，将删除的数据重新恢复回表JSS_TB1：

 
 
JSSPRE> INSERT INTO FLASH_TBL SELECT * FROM FLASH_TBL   
AS OF SCN 1257245 WHERE ID>10;  
10 rows created.  
JSSPRE> commit;  
Commit complete. 

使用SCN查询会比TIMESTAMP更加精确，事实上，即使执行Flashback Query时指定的是AS OF TIMESTAMP，Oracle也会将其转换成SCN，这是由于Oracle内部都是通过SCN来标记操作而不是时间。

不过在实际执行Flashback Query时，时间转换后具体对应哪个SCN，是通过SYS用户下的一个数据字典实现的，即SMON_SCN_TIME（时间与SCN的映射关系表）：

 
 
JSSPRE> DESC SYS.SMON_SCN_TIME;  
 Name               Null?       Type  
 ------------------     --------    ---------------  
 THREAD                         NUMBER  
 TIME_MP                        NUMBER  
 TIME_DP                        DATE 
 SCN_WRP                        NUMBER  
 SCN_BAS                        NUMBER  
 NUM_MAPPINGS               NUMBER  
 TIM_SCN_MAP                    RAW(1200)  
 SCN                            NUMBER  
 ORIG_THREAD                    NUMBER 

在10g中，系统平均每3秒产生一次系统时间与SCN的匹配并存入SYS.SMON_SCN_ TIME表，因此10g版本如果使用AS OF TIMESTAMP查询UNDO中的数据，实际获取的数据是以指定的时间对应的SCN时的数据为基准。

举个例子，如SCN:339988,339989分别匹配2009-05-30 13:52:00和2009-05-30 13:57:00，则当你通过AS OF TIMESTAMP查询2009-05-30 13:52:00或2009-05-30 13:56:59这段时间点内的任何时间，oracle都会将其匹配为SCN:339988到UNDO表空间中查找，也就说在这个时间内，不管你指定的时间点是什么，查询返回的都将是2009-05-30 13:52:00这个时刻对应的SCN的数据。如果通过上述文字的描述仍觉得不够形象，我想你亲自执行一下SELECT SCN,TO_CHAR（TIME_DP,'YYYY-MM-DD HH24:MI:SS'）FROM SYS.SMON_ SCN_TIME，会理解得更深刻一些。

在Oracle数据库中也可以手动进行时间和SCN的相互转换，Oracle提供了两个函数SCN_TO_TIMESTAMP和TIMESTAMP_TO_SCN专门干这个，例如：

 
 
JSSPRE> SELECT TIMESTAMP_TO_SCN(SYSDATE) FROM DUAL;  
TIMESTAMP_TO_SCN(SYSDATE)  
-------------------------  
                  1263291  
JSSPRE> SELECT TO_CHAR(SCN_TO_TIMESTAMP(1263291),   
'YYYY-MM-DD') FROM DUAL;  
TO_CHAR(SC  
----------  
2009-06-02 

提 示

上面的示例中TIMESTAMP类型经过TO_CHAR格式化，只显示了日期，千万别以为只能精确到日期哟。Oracle中的TIMESTAMP日期类型最大能够精确到纳秒（不过一般操作系统返回的精度只到毫秒，因此即使格式化显示出纳秒的精度也没意义，毫秒后就全是零了）。

看起来很强大吧？其实这两个函数的转换依赖于SYS.SMON_SCN_TIME表，能够转换到的最小SCN，也正是这个表中的最小记录，例如：

 
 
JSSPRE> SELECT SCN_WRP*4294967296+SCN_BAS FROM SYS.SMON_SCN_TIME  
  2  WHERE TIME_MP=(SELECT MIN(TIME_MP) FROM SYS.SMON_SCN_TIME);  
SCN_WRP*4294967296+SCN_BAS  
--------------------------  
                    554140 

能够转换到的最小SCN值是554140，使用SCN_TO_TIMESTAMP查询该SCN对应的时间：

 
 
JSSPRE> SELECT SCN_TO_TIMESTAMP(554140) FROM DUAL;  
SCN_TO_TIMESTAMP(554140)  
---------------------------------------------------------------------------  
21-MAR-09 11.51.30.000000000 PM 

比该SCN哪怕只再小1的值也无法转换了，因为SYS.SMON_SCN_TIME表中没有对应的映射关系：

 
 
JSSPRE> SELECT SCN_TO_TIMESTAMP(554139) FROM DUAL;  
select scn_to_timestamp(554139) from dual  
       *  
ERROR at line 1:  
ORA-08181: specified number is not a valid system change number  
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1 

时间的转换也是同理，所以你看，如果SYS.SMON_SCN_TIME表中不存在时间和SCN的映射关系，则执行函数转换时就会报错，也就是说时间和SCN之间并不存在绝对的对应关系。一切都是Oracle提供给你的，只有当它愿意让你看，你才能够看到。