平凡的幸福(備站）

一、recyclebin的使用範例（單一表格）

檢查recyclebin是否有啟用，VALUE=on表示有啟用
SQL> show parameter recyclebin ;

NAME TYPE VALUE
------------------- ----------- ----------
recyclebin string on

建立測試用的test_ccc並新增兩筆資料
SQL> CREATE TABLE apps.TEST_CCC(A VARCHAR2(20 BYTE)) ;
SQL> insert into test_ccc values ( 'may');
SQL> insert into test_ccc values ( 'john');
SQL> commit;

確認test_ccc是否有兩筆資料
SQL> select count(1) from test_ccc;

COUNT(1)
----------
2

刪除Table：test_ccc，不要加上purge的參數，purge是強制直接刪除
SQL> drop table test_ccc;
Table dropped.

重新查詢test_ccc，已經找不到Table
SQL> select count(1) from test_ccc;
select count(1) from test_ccc
*
ERROR at line 1:
ORA-00942: table or view does not exist

查詢回收站中是否有剛才刪除的Table
SQL> select OBJECT_NAME,ORIGINAL_NAME,OPERATION from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
BIN$+65JcJ65GHPgRAAUT/lsSQ==$0 TEST_CCC DROP
BIN$+sbBFDvWWtXgRKA2nyw7nA==$0 T1 DROP
BIN$+sEO43HLVgHgRKA2nyw7nA==$0 PLAN_TABLE DROP

還原被Drop的Table
SQL> flashback table test_ccc to before drop;
Flashback complete.

再查詢一次，Table及資料都還原回來了。
SQL> select count(1) from test_ccc;

COUNT(1)
----------
2



二、recyclebin的使用範例（同名表格）

再次刪除test_ccc
SQL> drop table test_ccc;

再建一個同名的表格
SQL> CREATE TABLE apps.TEST_CCC( A VARCHAR2(20 BYTE)) ;
SQL> insert into test_ccc values ( 'aa');
SQL> insert into test_ccc values ( 'bb');
SQL> insert into test_ccc values ( 'cc');
SQL> commit;

新建的test_ccc有三筆資料，而原有但已經刪除test_ccc有兩筆資料
SQL> select count(1) from test_ccc;

COUNT(1)
----------
3

將新建的test_ccc刪除
SQL> drop table test_ccc;

重新查詢recyclebin可以發現不同的時間有同名的表格
SQL> select ORIGINAL_NAME,OPERATION,droptime from user_recyclebin;

ORIGINAL_NAME OPERATION DROPTIME
-------------------------------- --------- -------------------
TEST_CCC DROP 2014-06-13:09:31:29
TEST_CCC DROP 2014-06-13:09:43:40
T1 DROP 2014-06-01:20:52:41
PLAN_TABLE DROP 2014-06-01:14:04:57

如下可得知雖然表格名稱相同，但是object_name是不同的。
SQL> select OBJECT_NAME,ORIGINAL_NAME from user_recyclebin
2 where original_name='TEST_CCC';

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$+65JcJ69GHPgRAAUT/lsSQ==$0 TEST_CCC
BIN$+65JcJ67GHPgRAAUT/lsSQ==$0 TEST_CCC

由recyclebin還原Test_ccc
SQL> flashback table test_ccc to before drop;

如下可以得知同名的表格會以最後被刪除的表格回復
SQL> select count(1) from test_ccc;

COUNT(1)
----------
3

此時再重新還原相同的表格時，就會出現物件已經存在的錯誤
SQL> flashback table test_ccc to before drop;
flashback table test_ccc to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object

如果想還原相同的表格，可以rename為另一個名稱
SQL> flashback table test_ccc to before drop rename to test_ccc_1;

也可以用object_name指定要還原的Table
FLASHBACK TABLE "BIN$+65JcJ65GHPgRAAUT/lsSQ==$0" TO BEFORE DROP;




三、清除recyclebin的資料及強制drop

清除指定表：purge table ;
清除目前用戶的recyclebin：purge recyclebin;
清除所有用户的recyclebin：purge dba_recyclebin;

查詢目前的recyclebin
SQL> select ORIGINAL_NAME,OPERATION,droptime,dropscn from user_recyclebin;

ORIGINAL_NAME OPERATION DROPTIME DROPSCN
-------------------------------- --------- ------------------- ----------
TEST_CCC DROP 2014-06-13:10:03:14 2.0602E+10
TEST_CCC_1 DROP 2014-06-13:10:34:09 2.0602E+10
T1 DROP 2014-06-01:20:52:41 2.0589E+10
PLAN_TABLE DROP 2014-06-01:14:04:57 15230154

清除recyclebin的test_ccc資料
SQL> purge table test_ccc;

Table purged.

SQL> select ORIGINAL_NAME,OPERATION,droptime from user_recyclebin;

ORIGINAL_NAME OPERATION DROPTIME
-------------------------------- --------- -------------------
TEST_CCC_1 DROP 2014-06-13:10:34:09
T1 DROP 2014-06-01:20:52:41
PLAN_TABLE DROP 2014-06-01:14:04:57

SQL> purge recyclebin;

Recyclebin purged.

SQL> select ORIGINAL_NAME,OPERATION,droptime,dropscn from user_recyclebin;

no rows selected

加上參數purge就可以強制drop table，不會到recyclebin，例如：
drop table test_ccc purge;


四、停用recyclebin

修改參數「recyclebin」來停用recyclebin，可以針對session或系統級別來設定：
alter session set recyclebin=off;
alter system set recyclebin=off;



五、recyclebin的額外說明

有幾種情形Drop是不會移動到recyclebin中
drop tablespace
drop a user
drop a cluster
drop a type

而recyclebin會佔用原本的表空間，除非表空間不足時，Oracle會由最早的recyclebin object開始回收