Oracle10g的回收站(recyclebin)和自由空间管理

今天在检查数据库报告时发现了这样一条记录：

- Large object Report OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE Size_Mb ---------- ----------------------------- ------------- ---------- ---------- BOSSMGR BIN$FzMEZaDyQK3gRAADuow9AA==$0 TABLE BOSSMGR 25075

在Oracle10g的 回收站里，竟然有一个大小为25G的对象，想着手手动释放这个空间，首先检查回收站内的相关对象：

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space 2 group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024 ------------------------------ -------------------- SYSTEM 190.5 DBMON 98.5625 USERS 93.875 BOSSMGR 27485 SYSAUX 90.625 UNDOTBS1 7726.625 6 rows selected. SQL> select * from ( 2 select a.owner,a.OBJECT_NAME,a.ORIGINAL_NAME,b.bytes/1024/1024 MB 3 from dba_recyclebin a,dba_segments b where a.object_name=b.segment_name 4 order by MB desc) where rownum <11; OWNER OBJECT_NAME ORIGINAL_NAME MB ------------ ------------------------------ -------------------------------- ---------- BOSSMGR BIN$FzMEZaDyQK3gRAADuow9AA==$0 SMS_ORG_9966_MT_BB 25075 BOSSMGR BIN$GEhw0fmlao/gRAADuow9AA==$0 SMS_USER_ACT_LT_D 150 BOSSMGR BIN$GQ9bLdyEMRXgRAADuow9AA==$0 TEM_HS_1000_MTREP 65 BOSSMGR BIN$GaA7x8y+dDrgRAADuow9AA==$0 TEM_HS_1000_MTREP 65 BOSSMGR BIN$F1VaFrYRJBfgRAADuow9AA==$0 STAT_RESPREPT_CENTER_TEM2 65 BOSSMGR BIN$F6luiSeSIurgRAADuow9AA==$0 TEM_HEBEI_0311 65 BOSSMGR BIN$F1VaFrYQJBfgRAADuow9AA==$0 PK_STAT_RESPREPT_CENTER_TEM2 45 BOSSMGR BIN$GaA7x8zHdDrgRAADuow9AA==$0 TEM_HS_1000_MTREP 35 BOSSMGR BIN$GY4HJpMhaVjgRAADuow9AA==$0 TEM_9966_USER 15 BOSSMGR BIN$GY4HJpMmaVjgRAADuow9AA==$0 TEM_9966_USER_2 15 10 rows selected.

清空最大的对象：

SQL> purge table bossmgr.SMS_ORG_9966_MT_BB; Table purged.

我们注意到此时的dba_free_space空间并未发生变化：

SQL> select tablespace_name,sum(bytes)/1024/1024 from dba_free_space 2 group by tablespace_name; TABLESPACE_NAME SUM(BYTES)/1024/1024 ------------------------------ -------------------- SYSTEM 190.5 DBMON 98.5625 USERS 93.875 BOSSMGR 27485 SYSAUX 90.625 UNDOTBS1 7726.625 6 rows selected.

那么是哪里发生了变化呢？

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/764/viewspace-120554/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/764/viewspace-120554/