救回已經刪除的 table ( FlashBack )

2016-08-10 12:08

SQL> select * from tab;  查現在有的 Table

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
TEST_TB                                                      TABLE

SQL> select count(*) from test_tb;  計算資料筆數

  COUNT(*)
----------
   1000000

SQL> drop table test_tb;  刪除 Table

Table dropped.
 

SQL> select OBJECT_NAME, ORIGINAL_NAME, OPERATION,droptime from user_recyclebin;  從垃圾桶中找到已刪除的 Table

OBJECT_NAME                                                  ORIGINAL_NAME                                                    OPERATION          DROPTIME
------------------------------------------------------------ ---------------------------------------------------------------- ------------------ --------------------------------------
BIN$ObDDHEGUlfTgUAB/AQA8lw==$0                               TEST_TB                                                          DROP               2016-08-10:12:17:57

SQL> select count(*) from "BIN$ObDDHEGUlfTgUAB/AQA8lw==$0";  計算資料筆數

  COUNT(*)
----------
   1000000

SQL> flashback table "BIN$ObDDHEGUlfTgUAB/AQA8lw==$0" to before drop;  救回已刪除的 Table

SQL> flashback table test_tb1 to before drop rename to TEST_TB1;  救回的 Table 可以 rename 成別的名稱

Flashback complete.

SQL> select * from tab;  查現在有的 Table

TNAME                                                        TABTYPE         CLUSTERID
------------------------------------------------------------ -------------- ----------
TEST_TB                                                      TABLE

SQL> select OBJECT_NAME, ORIGINAL_NAME, OPERATION,droptime from user_recyclebin; 垃圾桶中已經沒有救回來的 Table

no rows selected