eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

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

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

- 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')

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中对象太多的缘故。

 


历史上的今天...
    >> 2018-08-01文章:
    >> 2011-08-01文章:
    >> 2007-08-01文章:
           上海OOW上偶遇Rich Niemiec
           万恶的东方航空公司续
    >> 2005-08-01文章:

By eygle on 2006-08-01 16:40 | Comments (1) | Oracle12c/11g | 849 |

1 Comment

The Tabulation background of your artile is
so ugly and uncomfortable.

But I just like your article contents.


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com