檢查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 <table_name>;
清除目前用戶的recyclebin：purge recyclebin;
清除所有用户的recyclebin：purge dba_recyclebin;
查詢目前的recyclebin
SQL> select ORIGINAL_NAME,OPERATION,droptime,dropscn from user_recyclebin;
ORIGINAL_NAME OPERATION DROPTIME
-------------------------------- --------------- ----------------------
TEST_CCC DROP 2014-06-13:10:03:14
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
清除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開始回收
drop tablespace
drop a user
drop a cluster
drop a type
而recyclebin會佔用原本的表空間，除非表空間不足時，Oracle會由最早的recyclebin object開始回收
