ORACLE FLASHBACK QUERY 知识整理

来源：互联网发布：matlab中矩阵的转置编辑：程序博客网时间：2024/05/21 10:33

1 通用的闪回方法

SQL> select * from knight.books;//当前状态

ID NAME                      PRICE
------------------------------ ----------
       200 WEBWORK                   299.9
       201 PMP                       56.99
         1 LINUX                     25.55
         2 AIX                        50.5
         3 NETWORK                  125.89
         4 JAVA                      88.99
         5 JQUERY                   102.98
7 rowsselected.

SQL> select * from knight.books as of timestamp to_timestamp('2013-07-2720:56:33','yyyy-mm-dd hh24:mi:ss'); //查出该时间点的数据以及往前5分钟的数据，例子中的时间范围是17:55-18:00的记录。

       ID   NAME           PRICE
-------------------------- ----------
         1 LINUX                     25.55
         2 AIX                        50.5
         3 NETWORK                  125.89
         4 JAVA                      88.99
         5 JQUERY                   102.98

SQL> select * from knight.books minus select * from knight.books as of scn 839748; //查出该时间点与当前数据的差异。

ID        NAME       PRICE
------------------------ ----------
200       WEBWORK 299.9
201       PMP         56.99

/**针对dbms_flashback 的EXECUTE权限。SYS 用户不允许执行DBMS_FLASHBACK 包，将会产生ORA-08185 错误**/

SQL> execute dbms_flashback.enable_at_time(to_timestamp('2013-07-27 20:56:33','yyyy-mm-ddhh24:mi:ss'));//将整个会话都退回到该时间点，此时将禁止所有DML，只能SELECT，相当于整库回放。结束后一定要取消：SQL>execute dbms_flashback.disable;

BEGIN dbms_flashback.enable_at_time(to_timestamp('2013-07-27 20:56:33','yyyy-mm-ddhh24:mi:ss')); END;
*
ERROR at line1:
ORA-08185:Flashback not supported for user SYS
ORA-06512: at"SYS.DBMS_FLASHBACK", line 3
ORA-06512: atline 1

SQL> conn knight/knight

Connected.

SQL> execute dbms_flashback.enable_at_time(to_timestamp('2013-07-27 20:56:33','yyyy-mm-ddhh24:mi:ss'));

PL/SQ Lprocedure successfully completed.

SQL> select * from knight.books;//已经是历史的时间点数据了。

       ID NAME                      PRICE
------------------------------ ----------
         1 LINUX                     25.55
         2 AIX                        50.5
         3 NETWORK                  125.89
         4 JAVA                      88.99
         5 JQUERY                   102.98

SQL> execute dbms_flashback.disable;

PL/SQLprocedure successfully completed.

SQL> select * from knight.books;

       ID NAME                      PRICE
------------------------------ ----------
       200 WEBWORK                   299.9
       201 PMP                       56.99
         1 LINUX                     25.55
         2 AIX                        50.5
         3 NETWORK                  125.89
         4 JAVA                      88.99
         5 JQUERY                   102.98
7 rowsselected

2 VERSION

1) 不停机，无需配置，要求UNDO_RETENTION非0，可以通过权限禁用。

2) The Flashback Versions Query stops producing rows after it encounters a time in the past when thetable structure was changed

3) 使用对象的撤销数据

4) 允许查看某条记录的所有已提交版本。

5) 能够选择某条记录在一段时期内的所有版本，并能显示一个针对该记录的操作、操作时间、执行用户以及发生变化的事务标识符。

6) 查找某条记录的所有版本

SQL>select id,name,price,versions_xid,versions_startscn,versions_endscn,versions_operationfrom knight.books versions between scn minvalue and maxvalue where id=1;

7) 查找某条记录的指定时间段版本

SQL> selectid,name,price,versions_xid,versions_startscn,versions_endscn,versions_operationfrom knight.books versions between timestampto_timestamp('2013-07-28 23:08:00','yyyy-mm-dd hh24:mi:ss') andto_timestamp('2013-07-28 23:18:00','yyyy-mm-dd hh24:mi:ss')  where id=1;//查找过去10分钟内的记录的所有版本

3 TRANSACTION

1) 不停机，默认情况只有DBA角色可以使用。

2) 在11g需要配置：

SQL>alter database add supplemental logdata;

3) 使用事务的撤销数据

4) 可以构造倒退变化的SQL语句。

5) 没有出错的其他工作依然有效。

6) 除DBA外，如果想使用该功能，必须被授权select any transaction

首先需要根据VRESION的查询，找到待查找事务的VERSION_SID

SQL>select id,name,price,versions_xid from knight.books versions between scn minvalue and maxvalue where id=1;

然后根据VERSION_SID查找对应的事务。

SQL>select operation,undo_sql from flashback_transaction_query where xid=hextoraw(‘_VERSION_SID’);

返回的是撤销这个事务的SQL语句，而不是所有的事务。

4 TABLE

1) 闪回前一定要记录当前SCN

2) 不停机，无需配置，可以通过权限禁用

3) 使用对象的撤销数据

4) 针对某个表，倒退到指定时间点，该时间点之后的变化全部丢失,表上的触发器是禁用的。

5) 不影响其他表。

6) DDL无法闪回

7) SYS模式无法闪回

8) 闪回点之后删除的索引将不能恢复。

4.1 配置

/**被闪回的表必须启用记录转移，闪回多个表，则每个表都要启动**/

SQL>alter table knight.books enable row movement;

需要有FLASHBACKANY TABLE权限

注意：在发布FLASHBACKTABLE之前，需要记录当前的时间以及SCN号

4.2 举例

SQL> select current_scn from v$database;//确定当前SCN

CURRENT_SCN
-----------
866243

SQL> insert into knight.books values(301,'EJB',45); //插入新数据

1 row created.

SQL> commit;

Commit complete.

SQL> select * from knight.books; //查看一下

       ID NAME          PRICE
------------------ ----------
       200 WEBWORK       299.9
       201 PMP           56.99
       301 EJB              45
         1 STRUTS2         199
       300 DB2             299
         2 AIX            50.5
         3 NETWORK      125.89
         4 JAVA          88.99
         5 JQUERY       102.98
9 rows selected.

SQL> flashback table knight.books to scn 866243 enable trigger; //按照SCN闪回

Flashback complete.

SQL> select * from knight.books; //闪回成功

       ID NAME          PRICE
------------------ ----------
       200 WEBWORK       299.9
       201 PMP           56.99
         1 STRUTS2         199
       300 DB2             299
         2 AIX            50.5
         3 NETWORK      125.89
         4 JAVA          88.99
         5 JQUERY       102.98
8 rows selected.

4.3 外键因素

SQL> select * from knight.bookstore; //查询外键表

        ID NAME            ADDRESS
------------------------- --------------------
         1 NUM1            BEIJING
         2 NICE COFFEE     NY
         3 VK.STORE        MOSCOW

SQL> select * from knight.books; //查询主表，注意BOOKSTORE_ID为外键列，关联knight.bookstore的ID

       ID NAME                 PRICE BOOKSTORE_ID
------------------------- ---------- ------------
         1 PMP                  55.99            1
         2 JAVA                   129            1
         3 JQUERY                56.7            1
         4 DB2                    399            2
         5 FLEX                   599            2
         6 JAVA                  15.5            3
         7 DB2                     25            3
         8 LINUX                12.55            3
8 rows selected.

SQL> delete from knight.books where id=7; //主表内删除所有外键为3的数据

1 row deleted.

SQL> delete from knight.books where id in(6,8); //主表内删除所有外键为3的数据

2 rows deleted.

SQL> select * from knight.books; //查看主表新状态

       ID NAME                 PRICE BOOKSTORE_ID
------------------------- ---------- ------------
         1 PMP                  55.99            1
         2 JAVA                   129            1
         3 JQUERY                56.7            1
         4 DB2                    399            2
         5 FLEX                   599            2

SQL> delete from knight.bookstore where id=3; //删除外键表为3的记录

1 row deleted.

SQL> alter table knight.books enable row movement;// enable row movement

Table altered.

SQL> alter table knight.bookstore enable row movement; //enable row movement

Table altered.

SQL> flashback table knight.books to timestampto_timestamp('2013-07-29 00:10:00','yyyy-MM-dd hh24:mi:ss'); //因为外键的缘故，主表单独闪回失败

flashback table knight.books to timestamp to_timestamp('2013-07-29 00:10:00','yyyy-MM-ddhh24:mi:ss')
*
ERROR at line1:
ORA-02091:transaction rolled back
ORA-02291:integrity constraint (KNIGHT.BOOKS_BOOKSTORE_FK1) violated - parent
key not found

SQL> flashback table knight.books,knight.bookstore to timestampto_timestamp('2013-07-29 00:10:00','yyyy-MM-dd hh24:mi:ss'); //主表、外键表一起闪回成功

Flashback complete.

SQL> select * from knight.books; //查看主表

       ID NAME                PRICE BOOKSTORE_ID
------------------------- ---------- ------------
         1 PMP                  55.99            1
         2 JAVA                   129            1
         3 JQUERY                56.7            1
         4 DB2                    399            2
         5 FLEX                   599            2
         6 JAVA                  15.5            3
         7 DB2                     25            3
         8 LINUX                12.55            3
8 rowsselected.

SQL> select * from knight.bookstore; //查看外键表

       ID NAME            ADDRESS
------------------------- --------------------
         1 NUM1            BEIJING
         2 NICE COFFEE     NY
         3 VK.STORE        MOSCOW

4.4 可能的失败

1) 外键关联

2) 删除之后闪回之前，唯一键被复用，可能会引起违反约束。

3) 不存在足够的撤销信息（引起ORA-08180）

4) 受闪回影响的记录被其他用户锁定（引起ORA-00054）

5) 试图闪回DDL语句（DDL无法闪回，引起ORA-01466）

6) 视图闪回SYS模式的表（SYS模式无法闪回）

(完)

另请参见

ORACLE FLASHBACK DATABASE 知识整理

ORACLE FLASHBACK DROP 知识整理