eygle.com   eygle.com
eygle.com  
 

« 使用Oracle10g的Flashback Query进行数据闪回 | Blog首页 | 通过Oracle10g的flashback transaction query新特性进行事务撤销 »

Oracle10g的Flashback version Query

作者:eygle |【转载时请以超链接形式标明文章和作者信息及本声明
链接:
站内相关文章|Related Articles
Oracle10g通过Flashback Version Query提供查看对数据库事务级改变的方法.
当闪回事务处理查询与闪回版本查询同时使用时,我们可以轻易地从用户或者应用程序错误恢复。
以下是闪回版本查询的示例:
1.执行DML操作

EYGLE on 30-MAR-05 >create table t as select username,user_id from dba_users; Table created. EYGLE on 30-MAR-05 >select * from t; USERNAME USER_ID ------------------------------ ---------- SYSTEM 5 SYS 0 TEST 25 EYGLE 26 SCOTT 29 DIP 19 TRANS 27 TEST1 28 OPERATOR 31 WMSYS 23 DBSNMP 22 USERNAME USER_ID ------------------------------ ---------- OUTLN 11 12 rows selected. EYGLE on 30-MAR-05 >delete from t where username='OUTLN'; 1 row deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >delete from t where username='TEST1'; 1 row deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select * from t; USERNAME USER_ID ------------------------------ ---------- SYSTEM 5 SYS 0 TEST 25 EYGLE 26 SCOTT 29 DIP 19 TRANS 27 OPERATOR 31 WMSYS 23 DBSNMP 22 10 rows selected. EYGLE on 30-MAR-05 >update t set user_id=1 where username='EYGLE'; 1 row updated. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >delete from t where user_id >10; 7 rows deleted. EYGLE on 30-MAR-05 >commit; Commit complete. EYGLE on 30-MAR-05 >select * from t; USERNAME USER_ID ------------------------------ ---------- SYSTEM 5 SYS 0 EYGLE 1 EYGLE on 30-MAR-05 >insert into t values('PENNY',2); 1 row created. EYGLE on 30-MAR-05 >commit; Commit complete.

2.执行闪回版本查询

EYGLE on 30-MAR-05 >select versions_starttime, versions_endtime, versions_xid, 2 versions_operation, username,user_id 3 from t versions between timestamp minvalue and maxvalue 4 / VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID ------------------------------ ------------------------------ ---------------- - ---------- ---------- 30-MAR-05 09.34.49 AM 000A000B000000F1 D DBSNMP 22 30-MAR-05 09.34.49 AM 000A000B000000F1 D WMSYS 23 30-MAR-05 09.34.49 AM 000A000B000000F1 D OPERATOR 31 30-MAR-05 09.34.49 AM 000A000B000000F1 D TRANS 27 30-MAR-05 09.34.49 AM 000A000B000000F1 D DIP 19 30-MAR-05 09.34.49 AM 000A000B000000F1 D SCOTT 29 30-MAR-05 09.34.49 AM 000A000B000000F1 D TEST 25 30-MAR-05 09.34.15 AM 0001001900000F0F U EYGLE 1 30-MAR-05 09.33.51 AM 00080016000000EF D TEST1 28 30-MAR-05 09.33.23 AM 0004000A000005EF D OUTLN 11 SYSTEM 5 VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID ------------------------------ ------------------------------ ---------------- - ---------- ---------- SYS 0 30-MAR-05 09.34.49 AM TEST 25 30-MAR-05 09.34.15 AM EYGLE 26 30-MAR-05 09.34.49 AM SCOTT 29 30-MAR-05 09.34.49 AM DIP 19 30-MAR-05 09.34.49 AM TRANS 27 30-MAR-05 09.33.51 AM TEST1 28 30-MAR-05 09.34.49 AM OPERATOR 31 30-MAR-05 09.34.49 AM WMSYS 23 30-MAR-05 09.34.49 AM DBSNMP 22 30-MAR-05 09.33.23 AM OUTLN 11 VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V USERNAME USER_ID ------------------------------ ------------------------------ ---------------- - ---------- ---------- 30-MAR-05 09.49.24 AM 00080006000000EF I PENNY 2 23 rows selected. EYGLE on 30-MAR-05 >

我们可以看到,以上事务的时间以及数据更改。


历史上的今天...
      >> 2009-03-30文章:
             关于ocssd进程的三言两语
      >> 2008-03-30文章:
             resize datafile 与 checkpoint
      >> 2006-03-30文章:
             广告: 招聘SQL SERVER DBA
------
这篇 【Oracle10g的Flashback version Query】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅

By eygle on 2005-03-30 09:56 | Comments (0) | Posted to Backup&Recovery | Edit |

相关文章 随机文章
2006 年终总结-个人网站的发展总结
Itpub技术丛书《Oracle数据库性能优化》已经开始预订
姓氏、染色体与传统文化
RAC + ASM单节点添加表空间的BUG
春天的花开以及冬日的冰
搜索本站:

留言 (0)

发表留言:



Remember Me?
(输入验证码后方可评论,谢谢支持)



CopyRight © 2004~2010 eygle.com, All rights reserved.