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

eygle發表於2019-07-01
Oracle

今天在檢查資料庫報告時發現了這樣一條記錄：

- 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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章