10g flashback table學(xué)習(xí)

168一路發(fā) 2011-03-22

展開全文

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 行出現(xiàn)錯誤:
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;
```