近日為某客戶做資料庫巡檢，發現資料庫recycle bin中有幾千個被删除的對象，于是建議客戶清理recycle bin中的對象，釋放空間，客戶問這些對象占用空間嗎？存放在哪裡的？
這些被删除的對象當然是存放在硬碟上的，官方方檔介紹recycle bin 如下：
The recycle bin is actually a data dictionary table containing information about dropped objects. Dropped tables and any associated objects such as indexes,
constraints, nested tables, and the likes are not removed and still occupy space. They continue to count against user space quotas, until specifically purged
from the recycle bin or the unlikely situation where they must be purged by the database because of tablespace space constraints
recycle bin事實上是一個資料字典表，包含被删除對象的資訊，例如：表、索引、限制等，recycle bin中被删除的對象不會被remove,仍然占用空間。
Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the
recycle bin are those that the user owns. A user can view his objects in the recycle bin using the following statement:SELECT * FROM RECYCLEBIN;
每個使用者都有自己的recycle bin，對象的擁有者都可以通路自己的recycle bin，除非具有sysdba權限，可以通過select * from recyclebin查詢，預設recycle bin是開啟的。
1、禁用recycle bin:
ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;
2、開啟recycle bin:
ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON;
測試：
1、建立表空間
[email protected]>create tablespace recycle datafile '/u01/app/oracle/oradata/jzh/recycle' size 10M;
Tablespace created.
2、建立用與測試表
[email protected]>create user test identified by test default tablespace recycle;
User created.
[email protected]>grant dba to test;
Grant succeeded.
[email protected]>conn test/test
Connected.
[email protected]>create table test as select * from dba_objects;
Table created.
3、删除test表
[email protected]>select * from recyclebin;
no rows selected
[email protected]>drop table test;
Table dropped.
[email protected]>select OBJECT_NAME,ORIGINAL_NAME,OPERATION,TYPE,DROPTIME from recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME
------------------------------ -------------------------------- --------- ------------------------- -------------------
BIN$GJ+BrKeuDfDgU28BqMAPIg==$0 TEST DROP TABLE 2015-06-16:16:39:03
4、查詢test使用者下對象
[email protected]>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$GJ+BrKeuDfDgU28BqMAPIg==$0 TABLE
5、查詢對象BIN$GJ+BrKeuDfDgU28BqMAPIg==$0所在表空間，大小等資訊
[email protected]>select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES,BLOCKS from user_segments where segment_name='BIN$GJ+BrKeuDfDgU28BqMAPIg==$0';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES BLOCKS
--------------------------------------------------------------------------------- ------------------ ------------------------------ ---------- ----------
BIN$GJ+BrKeuDfDgU28BqMAPIg==$0 TABLE RECYCLE 8716288 1064
以上可以看出BIN$GJ+BrKeuDfDgU28BqMAPIg==$0對象依然存儲在recycle表空間，大小為8716288位元組，共1064個blocks
總結：1、被drop之後的表依然存儲在原位置，大小無變化，占用空間；
2、在原位置被drop的對象被标記為可覆寫；
3、當原表空間無free的空間時，該位置就會被覆寫；