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

« [赏画]范曾-牛郎织女会七夕 | Blog首页 | 首届杰出数据库工程师评选文集 »

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

- 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.

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

变化发生在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')

而在Oracle10g中，这个视图增加了关于recyclebin的部分：

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