适用于：
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.8 - Release: 9.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2010***
目标：
该报告提供了一个提高闪回（在9i中有介绍）特性的示例代码
解决方案：
闪回版本查询：
————
使用数据库中的undo数据，可以查看一行或者多行的包括元数据的改变。使用VERSIONS BETWEEN语句闪回版本查询可以使得指定的版本在一个窗口时间内被追踪：
CREATE TABLE flashback_version_query (id NUMBER(10), description VARCHAR2(50));
INSERT INTO flashback_version_query (id, description) VALUES (1, 'Kunal');
COMMIT;
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725202 2004-03-29 14:59:08
UPDATE flashback_version_query SET description = 'John' WHERE id = 1;
COMMIT;
UPDATE flashback_version_query SET description = 'Mathew' WHERE id = 1;
COMMIT;
SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;
CURRENT_SCN TO_CHAR(SYSTIMESTAM
----------- -------------------
725219 2004-03-29 14:59:36
COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
SET LINESIZE 200
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query
VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-04-27 15:13:57', 'YYYY-MM-DD HH24:MI:SS')
AND TO_TIMESTAMP('2004-04-27 15:14:52', 'YYYY-MM-DD HH24:MI:SS')
WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
8183217632829 27-APR-04 03.14.44 PM 0006001000000250 U Mathew
8183217632826 27-APR-04 03.14.44 PM 8183217632829 27-APR-04 03.14.44 PM 0002000D00001347 U John
8183217632826 27-APR-04 03.14.44 PM Kunal
SELECT versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation,
description
FROM flashback_version_query
VERSIONS BETWEEN SCN 8183217632826 AND 8183217632829
WHERE id = 1;
VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION
------------------ ------------------------ ------------------ ------------------------ ---------------- - -----------
8183217632829 27-APR-04 03.14.44 PM 0006001000000250 U Mathew
8183217632826 27-APR-04 03.14.44 PM 8183217632829 27-APR-04 03.14.44 PM 0002000D00001347 U John
8183217632826 27-APR-04 03.14.44 PM Kunal
可用的伪列是：
* VERSIONS_STARTSCN 或VERSIONS_STARTTIME –当行取这个值时值，启用SCN和TIMESTAMPD。如果行在更低绑定SCN或者TIMESTAMP之前被创建，那么将返回空值。
* VERSIONS_ENDSCN 或 VERSIONS_ENDTIME –当行最后包含这个值时，中止SCN和TIMESTAMP ，如果该行的值当前仍旧在绑定SCN或者TIMESTAMP上的话，那么将返回空值。
* VERSIONS_XID – 创建行当在它前状态的事务ID
* VERSIONS_OPERATION – 事务执行的操作((I)nsert, (U)pdate or (D)elete)
闪回事务查询
————
闪回事务查询可以被用于得到更多关于事务额外的被闪回版本查询列出的信息。来自闪回版本查询VERSIONS_XID列的值可以用于查询FLASHBACK_TRANSACTION_QUERY视图：
SQL> SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql
FROM flashback_transaction_query
WHERE xid = HEXTORAW('0006001000000250'); 2 3
XID OPERATION START_SCN COMMIT_SCN
---------------- -------------------------------- ---------- ----------
LOGON_USER
------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
0006001000000250 UPDATE 8.1832E+12 8.1832E+12
KUNAL
update "KUNAL"."FLASHBACK_VERSION_QUERY" set "DESCRIPTION" = 'John' where ROWID
= 'AAAOBfAAIAAAAnaAAA';
0006001000000250 BEGIN 8.1832E+12 8.1832E+12
KUNAL
显示相关信息
产品
* Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键词
FEATURES; FLASHBACK
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-29 10:33:09 |
赞(
操作
