加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 (https://www.lidatong.com.cn/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 百科 > 正文

数据回滚:基于时间的查询(AS OF TIMESTAMP)和(AS OF ACN)

发布时间:2020-12-12 15:50:18 所属栏目:百科 来源:网络整理
导读:http://blog.csdn.net/zhouwubin123/article/details/6617837 转载自 1.应用Flashback Query查询过去的数据 Flashback Query这一特性, 最常被应用的就是修复误操作的数据 了。注意,这并不是说Flashback Query能够恢复数据。Flashback Query本身不会恢复任

http://blog.csdn.net/zhouwubin123/article/details/6617837

转载自

1.应用Flashback Query查询过去的数据

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

1.1基于时间的查询(AS OF TIMESTAMP)

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

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

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

提 示

SYSDATE-5/1440是啥意思,1440又是怎么来的?

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

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

JSSPRE> INSERT INTO FLASH_TBL SELECT * FROM FLASH_TBL AS OF TIMESTAMP SYSDATE-5/1440 3 WHERE ID<10;

9 rows created.

JSSPRE> COMMIT;

Commit complete. 成功插入9条记录,查询表中当前的记录:

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


转载自:http://blog.sina.com.cn/s/blog_6d6e54f70100z7yd.html 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包的权限:

  1. JSSPRE> CONN/ASSYSDBA
  2. Connected.
  3. JSSPRE>GRANTEXECUTEONDBMS_FLASHBACKTOSCOTT;
  4. Grantsucceeded.

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

  1. JSSPRE> CONN/ASSYSDBA
  2. Connected.
  3. JSSPRE>GRANTSELECTONV_$DATABASETOSCOTT;
  4. Grantsucceeded.

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

  1. JSSPRE>SELECTDBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBERFROMDUAL;
  2. GET_SYSTEM_CHANGE_NUMBER
  3. ------------------------
  4. 1257245
  5. JSSPRE>DELETEFLASH_TBLWHEREID>10;
  6. 10rowsdeleted.
  7. JSSPRE>COMMIT;
  8. Commitcomplete.

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

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

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

  1. JSSPRE>INSERTINTOFLASH_TBLSELECT*FROMFLASH_TBL
  2. ASOFSCN 1257245WHEREID>10;
  3. 10rowscreated.
  4. JSSPRE>commit;
  5. Commitcomplete.

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

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

  1. JSSPRE>DESCSYS.SMON_SCN_TIME;
  2. NameNull? Type
  3. ------------------ -------- ---------------
  4. THREAD NUMBER
  5. TIME_MP NUMBER
  6. TIME_DPDATE
  7. SCN_WRP NUMBER
  8. SCN_BAS NUMBER
  9. NUM_MAPPINGS NUMBER
  10. TIM_SCN_MAP RAW(1200)
  11. SCN NUMBER
  12. 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专门干这个,例如:

  1. JSSPRE>SELECTTIMESTAMP_TO_SCN(SYSDATE)FROMDUAL;
  2. TIMESTAMP_TO_SCN(SYSDATE)
  3. -------------------------
  4. 1263291
  5. JSSPRE>SELECTTO_CHAR(SCN_TO_TIMESTAMP(1263291),
  6. 'YYYY-MM-DD')FROMDUAL;
  7. TO_CHAR(SC
  8. ----------
  9. 2009-06-02

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

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

  1. JSSPRE>SELECTSCN_WRP*4294967296+SCN_BASFROMSYS.SMON_SCN_TIME
  2. 2WHERETIME_MP=(SELECTMIN(TIME_MP)FROMSYS.SMON_SCN_TIME);
  3. SCN_WRP*4294967296+SCN_BAS
  4. --------------------------
  5. 554140

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

  1. JSSPRE>SELECTSCN_TO_TIMESTAMP(554140)FROMDUAL;
  2. SCN_TO_TIMESTAMP(554140)
  3. ---------------------------------------------------------------------------
  4. 21-MAR-09 11.51.30.000000000 PM

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

  1. JSSPRE>SELECTSCN_TO_TIMESTAMP(554139)FROMDUAL;
  2. selectscn_to_timestamp(554139)fromdual
  3. *
  4. ERRORatline 1:
  5. ORA-08181: specified numberisnota valid system change number
  6. ORA-06512:at"SYS.SCN_TO_TIMESTAMP",line 1

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

(编辑:李大同)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    推荐文章
      热点阅读