根据官方文档中解释,recycle bin中删除的对象也是占空间的,实验如下:
1、创建10m的表空间
SYS@jzh>create tablespace recycle datafile '/u01/app/oracle/oradata/jzh/recycle' size 10M;
Tablespace created.
SYS@jzh>select total.tablespace_name,
2 round(total.MB, 2) as Total_MB,
3 round(total.MB - free.MB, 2) as Used_MB,
4 round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
5 (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
6 (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
7 where free.tablespace_name = total.tablespace_name order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
SYSTEM 720 712.19 98.91%
UNDOTBS1 785 773 98.47%
USERS 613.75 600.19 97.79%
TEST 256.38 228.19 89.01%
SYSAUX 600 523.44 87.24%
RECYCLE 10 1 10%-------------》使用了10%
QUEST 500 5.94 1.19%
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、再次查询表空间recycle使用情况
SYS@jzh>select total.tablespace_name,
2 round(total.MB, 2) as Total_MB,
3 round(total.MB - free.MB, 2) as Used_MB,
4 round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
5 (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
6 (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
7 where free.tablespace_name = total.tablespace_name order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
SYSTEM 720 712.19 98.91%
UNDOTBS1 785 773 98.47%
USERS 613.75 600.19 97.79%
RECYCLE 10 9.31 93.13%---------------------》使用了93.13%
TEST 256.38 228.19 89.01%
SYSAUX 600 523.44 87.24%
QUEST 500 5.94 1.19%
7 rows selected.
4、删除表
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$GJ9xNATwDd3gU28BqMA9oA==$0 TEST DROP TABLE 2015-06-16:16:34:26
5、再次查询表空间使用情况
SYS@jzh>select total.tablespace_name,
2 round(total.MB, 2) as Total_MB,
3 round(total.MB - free.MB, 2) as Used_MB,
4 round((1 - free.MB / total.MB) * 100, 2) || '%' as Used_Pct from
5 (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_free_space group by tablespace_name) free,
6 (select tablespace_name,sum(bytes) / 1024 / 1024 as MB from dba_data_files group by tablespace_name) total
7 where free.tablespace_name = total.tablespace_name order by used_pct desc;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- -----------------------------------------
SYSTEM 720 712.19 98.91%
UNDOTBS1 785 773 98.47%
USERS 613.75 600.19 97.79%
TEST 256.38 228.19 89.01%
SYSAUX 600 523.44 87.24%
RECYCLE 10 1 10%-----------------------------》使用率降下来了
QUEST 500 5.94 1.19%
7 rows selected.
删除表之后,表空间使用率又降下来了
Therecycle 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的对象仍然会占用空间,这是怎么回事?