10g flashback table学习

SQL> var scn number

SQL> exec :scn := dbms_flashback.get_system_change_number

PL/SQL 过程已成功完成。

SQL> select count(*) from t;

  COUNT(*)
----------
        26

SQL> delete from t;

已删除26行。

SQL> commit;

提交完成。

SQL> select count(*) from t as of scn :scn;

  COUNT(*)
----------
        26

SQL> select * from (select count(*) from t), (select count(*) from t as of scn :
scn);

  COUNT(*)   COUNT(*)
---------- ----------
         0         26

SQL> flashback table t to scn :scn;
flashback table t to scn :scn
                *
第 1 行出现错误:
ORA-08189: 因为未启用行移动功能, 不能闪回表

SQL> select * from (select count(*) from t), (select count(*) from t as of scn :

  2  scn);

  COUNT(*)   COUNT(*)
---------- ----------
        26         26

 

SQL>  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。
SQL> flashback table t to timestamp to_timestamp('2007-03-30:15:24:19','yyyy-m
m-dd:hh24:mi:ss');

 Prerequisites for Using Flashback Table

The prerequisites for performing a FLASHBACK TABLE operation are as follows:

• You must have been granted the FLASHBACK ANY TABLE system privilege or you must have the FLASHBACK object privilege on the table.
• You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
• Undo information retained in the undo tablespace must go far enough back in time to satisfy the specified target point in time or SCN for the FLASHBACK TABLE operation.
• Row movement must be enabled on the table for which you are issuing the FLASHBACK TABLE statement. You can enable row movement with the following SQL statement:

  ALTER TABLE table ENABLE ROW MOVEMENT;