Oracle Flashback Version Query

最新推荐文章于 2024-10-14 10:36:17 发布

ZhongWeicheng

最新推荐文章于 2024-10-14 10:36:17 发布

阅读量577

点赞数

分类专栏： Oracle 文章标签： oracle Oracle ORACLE

本文链接：https://blog.csdn.net/staricqxyz/article/details/8314533

版权

Oracle 专栏收录该内容

165 篇文章 0 订阅

订阅专栏

flashback version query能够找到所有已经提交的行记录,我们可以清晰的看到何时执行了什么操作。

确保AUM打开

SQL> SHOW parameter undo_m

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO

SQL> SET TIME ON
20:59:40 SQL> SHOW USER
USER IS "ZWC"
20:59:44 SQL> SELECT * FROM t_user;

USERID     USERNAME   REGDATE    LASTWRITETIME        EMAIL                TEL         PASSWORD
---------- ---------- ---------- -------------------- -------------------- ----------- ----------
001        jarry      10-JAN-12  12-JAN-12            jarry@sina.com       13814010000 JarpwD
002        tom        10-JAN-12  14-JAN-12            tom@126.com          13440099509 ToMpWD

21:01:41删除一条记录

21:01:27 SQL> DELETE FROM t_user WHERE userid = '002';

1 ROW deleted.

21:01:41 SQL> commit;

Commit complete.

21:01:43 SQL> SELECT * FROM t_user;

USERID     USERNAME   REGDATE    LASTWRITETIME        EMAIL                TEL         PASSWORD
---------- ---------- ---------- -------------------- -------------------- ----------- ----------
001        jarry      10-JAN-12  12-JAN-12            jarry@sina.com       13814010000 JarpwD

21:03:18删除一条记录

21:03:15 SQL> DELETE FROM t_user WHERE userid = '001';

1 ROW deleted.

21:03:18 SQL> commit;

Commit complete.

21:03:22 SQL> SELECT * FROM t_user;

no ROWS selected

使用flashback version query,versions_operation为D说明是delete操作

21:06:37 SQL> SELECT username,password,email,versions_operation,versions_xid,versions_starttime 
21:06:41   2  FROM t_user
21:06:45   3  versions BETWEEN TIMESTAMP minvalue AND maxvalue ORDER BY userid,versions_starttime;

USERNAME   PASSWORD   EMAIL                V VERSIONS_XID     VERSIONS_STARTTIME
---------- ---------- -------------------- - ---------------- ---------------------------------------------------------------------------
jarry      JarpwD     jarry@sina.com       D 01001700DF020000 11-JAN-12 09.03.20 PM
jarry      JarpwD     jarry@sina.com
tom        ToMpWD     tom@126.com          D 07001800D5020000 11-JAN-12 09.01.38 PM
tom        ToMpWD     tom@126.com

恢复删除数据

21:16:19 SQL> INSERT INTO t_user SELECT DISTINCT * FROM t_user versions BETWEEN TIMESTAMP minvalue AND maxvalue;

2 ROWS created.

21:16:33 SQL> commit;

Commit complete.

21:16:35 SQL> SELECT * FROM t_user;

USERID     USERNAME   REGDATE    LASTWRITETIME        EMAIL                TEL         PASSWORD
---------- ---------- ---------- -------------------- -------------------- ----------- ----------
001        jarry      10-JAN-12  12-JAN-12            jarry@sina.com       13814010000 JarpwD
002        tom        10-JAN-12  14-JAN-12            tom@126.com          13440099509 ToMpWD