近日為某客戶做資料庫巡檢，發現資料庫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、建立表空間
SYS@jzh>create tablespace recycle datafile '/u01/app/oracle/oradata/jzh/recycle' size 10M;
Tablespace created.
2、建立用與測試表
SYS@jzh>create user test identified by test default tablespace recycle;
User created.
SYS@jzh>grant dba to test;
Grant succeeded.
SYS@jzh>conn test/test
Connected.
TEST@jzh>create table test as select * from dba_objects;
Table created.
3、刪除test表
TEST@jzh>select * from recyclebin;
no rows selected
TEST@jzh>drop table test;
Table dropped.
TEST@jzh>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使用者下物件
TEST@jzh>select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$GJ+BrKeuDfDgU28BqMAPIg==$0 TABLE
5、查詢物件BIN$GJ+BrKeuDfDgU28BqMAPIg==$0所在表空間，大小等資訊
TEST@jzh>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的空間時，該位置就會被覆蓋；

Oracle recyclebin介紹

相關文章