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

- Large object Report

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE Size_Mb
---------- ----------------------------- ------------- ---------- ----------
BOSSMGR BIN$FzMEZaDyQK3gRAADuow9AA== TABLE BOSSMGR 25075

在Oracle10g的裡，竟然有一個大小為25G的物件，想著手手動釋放這個空間，首先檢查回收站內的相關物件：

[@more@]

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== SMS_ORG_9966_MT_BB 25075
BOSSMGR BIN$GEhw0fmlao/gRAADuow9AA== SMS_USER_ACT_LT_D 150
BOSSMGR BIN$GQ9bLdyEMRXgRAADuow9AA== TEM_HS_1000_MTREP 65
BOSSMGR BIN$GaA7x8y+dDrgRAADuow9AA== TEM_HS_1000_MTREP 65
BOSSMGR BIN$F1VaFrYRJBfgRAADuow9AA== STAT_RESPREPT_CENTER_TEM2 65
BOSSMGR BIN$F6luiSeSIurgRAADuow9AA== TEM_HEBEI_0311 65
BOSSMGR BIN$F1VaFrYQJBfgRAADuow9AA== PK_STAT_RESPREPT_CENTER_TEM2 45
BOSSMGR BIN$GaA7x8zHdDrgRAADuow9AA== TEM_HS_1000_MTREP 35
BOSSMGR BIN$GY4HJpMhaVjgRAADuow9AA== TEM_9966_USER 15
BOSSMGR BIN$GY4HJpMmaVjgRAADuow9AA== 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.

那麼是哪裡發生了變化呢？

變化發生在dba_free_space檢視的構成上。在Oracle10g中，如果啟用flash drop功能，在drop表時，資料庫不會直接刪除，而是將其放在回收站中，當空間出現短缺時，才會逐漸回收這部分空間。當物件刪除之後，這部分空間會記入free space，也就是說被包含在dba_free_space的查詢結果之中。

我們看一下這個檢視的變化，在Oracle9i之中，這個檢視構建如下：

SQL> set long 9999
SQL> set pagesize 99
SQL> select text from dba_views where view_name=upper('&view_name');
Enter value for view_name: dba_free_space
old 1: select text from dba_views where view_name=upper('&view_name')
new 1: select text from dba_views where view_name=upper('dba_free_space')

TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0

而在Oracle10g中，這個檢視增加了關於recyclebin的部分：

SQL> set long 9999
SQL> set pagesize 99
SQL> select text from dba_views where view_name=upper('&view_name');
Enter value for view_name: dba_free_space
old 1: select text from dba_views where view_name=upper('&view_name')
new 1: select text from dba_views where view_name=upper('dba_free_space')

TEXT
--------------------------------------------------------------------------------
select ts.name, fi.file#, f.block#,
f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
and f.ts# = fi.ts#
and f.file# = fi.relfile#
and ts.bitmapped = 0
union all
select /*+ ordered use_nl(f) use_nl(fi) */
ts.name, fi.file#, f.ktfbfebno,
f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
and f.ktfbfetsn = fi.ts#
and f.ktfbfefno = fi.relfile#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select /*+ ordered use_nl(u) use_nl(fi) */
ts.name, fi.file#, u.ktfbuebno,
u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
and rb.ts# = fi.ts#
and rb.file# = fi.relfile#
and u.ktfbuesegtsn = rb.ts#
and u.ktfbuesegfno = rb.file#
and u.ktfbuesegbno = rb.block#
and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
union all
select ts.name, fi.file#, u.block#,
u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
and u.ts# = fi.ts#
and u.segfile# = fi.relfile#
and u.ts# = rb.ts#
and u.segfile# = rb.file#
and u.segblock# = rb.block#
and ts.bitmapped = 0

這就是Oracle10g的回收站(recyclebin)和自由空間管理的變化。

在Oracle10g中，有時候查詢dba_free_space檢視異常緩慢，就是因為recyclebin中物件太多的緣故。

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

相關文章