【IT168 技术文档】

背景：
OS:AIX 4.3.3

ORACLE:8.1.7.4 compatible:8.1.0

一计费系统，有个数据字典管理的表空间有170多G。客户已将其中的数据全部迁移到一个新的local管理的表空间(还剩一个临时段)，打算将这个表空间drop掉。但是已偿试了两个晚上，都无法drop掉。于是空间十分紧张，急需要将这个表空间释放出来。

检查这个表空间的碎片，吓了一跳，居然有124万多个。怪不得整个晚上都无法drop掉(为了不影响业务，只能是晚上drop，白天必须停止)。
一时间，脑海里面冒出以下几个思路：

1.修改fet$基表，结合碎片。
2.alter tablespace ... coalesce;
3.将表空间由字典转换成local。
4.将每个数据文件resize。

首先偿试一个晚上coalesce。但是第二天来的时候，一个晚上才结合了1万多个碎片。这样的速度是无法接受的。

中午时分试了一下Resize，也是很慢。不过我估计一个晚上还是能做几个数据文件的resize.

偿试将表空间转换成local，但是不幸的是需要compatible为8.1.6以上。当时数据库是从低版本升上来的，compatible一直没有调高，这样调高有可能引响业务。所以也不敢轻易地调整。

剩下是修改fet$基表。oracle结合碎片的思路其实是挺简单的，将连续的碎片结合成一块，修改length长度即可。

这个表空间有43个文件，那个临时段占用了5个文件。其余38个文件都是空的，那么在fet$表中应该就是一条记录,block#为2,length为数据文件的blocks-1。

这样首先将这个表空间offline掉，这个表空间在fet$表中的数据就不会发生改变了。

首先处理那38个空闲文件：

首先查找完全空闲的数据文件及其blocks的总数：

select file_id,file_name,bytes/1024/1024,blocks-1 from dba_data_files where tablespace_name='TB_CDR'
and file_id not in (select file_id from dba_extents where tablespace_name='TB_CDR')

手工修改fet$基表：

delete fet$ where ts#=13 and block# > 2 and file#=14 ;
delete fet$ where ts#=13 and block# > 2 and file#=18 ;
delete fet$ where ts#=13 and block# > 2 and file#=24 ;
delete fet$ where ts#=13 and block# > 2 and file#=25 ;
delete fet$ where ts#=13 and block# > 2 and file#=26 ;
delete fet$ where ts#=13 and block# > 2 and file#=27 ;
delete fet$ where ts#=13 and block# > 2 and file#=28 ;
delete fet$ where ts#=13 and block# > 2 and file#=41 ;
delete fet$ where ts#=13 and block# > 2 and file#=42 ;
delete fet$ where ts#=13 and block# > 2 and file#=43 ;
delete fet$ where ts#=13 and block# > 2 and file#=44 ;
delete fet$ where ts#=13 and block# > 2 and file#=54 ;
delete fet$ where ts#=13 and block# > 2 and file#=55 ;
delete fet$ where ts#=13 and block# > 2 and file#=58 ;
delete fet$ where ts#=13 and block# > 2 and file#=60 ;
delete fet$ where ts#=13 and block# > 2 and file#=61 ;
delete fet$ where ts#=13 and block# > 2 and file#=63 ;
delete fet$ where ts#=13 and block# > 2 and file#=64 ;
delete fet$ where ts#=13 and block# > 2 and file#=66 ;
delete fet$ where ts#=13 and block# > 2 and file#=67 ;
delete fet$ where ts#=13 and block# > 2 and file#=72 ;
delete fet$ where ts#=13 and block# > 2 and file#=73 ;
delete fet$ where ts#=13 and block# > 2 and file#=76 ;
delete fet$ where ts#=13 and block# > 2 and file#=77 ;
delete fet$ where ts#=13 and block# > 2 and file#=78 ;
delete fet$ where ts#=13 and block# > 2 and file#=79 ;
delete fet$ where ts#=13 and block# > 2 and file#=80 ;
delete fet$ where ts#=13 and block# > 2 and file#=81 ;
delete fet$ where ts#=13 and block# > 2 and file#=83 ;
delete fet$ where ts#=13 and block# > 2 and file#=106;
delete fet$ where ts#=13 and block# > 2 and file#=107;
delete fet$ where ts#=13 and block# > 2 and file#=108;
delete fet$ where ts#=13 and block# > 2 and file#=109;
delete fet$ where ts#=13 and block# > 2 and file#=110;
delete fet$ where ts#=13 and block# > 2 and file#=111;
delete fet$ where ts#=13 and block# > 2 and file#=123;
delete fet$ where ts#=13 and block# > 2 and file#=124;
delete fet$ where ts#=13 and block# > 2 and file#=125;

length为数据文件的blocks数量减1：

update fet$ set length=511999 where ts#=13 and block#=2 and file#=14 ;
update fet$ set length=511999 where ts#=13 and block#=2 and file#=18 ;
update fet$ set length=511999 where ts#=13 and block#=2 and file#=24 ;
update fet$ set length=511999 where ts#=13 and block#=2 and file#=25 ;
update fet$ set length=511999 where ts#=13 and block#=2 and file#=26 ;
update fet$ set length=511999 where ts#=13 and block#=2 and file#=27 ;
update fet$ set length=511999 where ts#=13 and block#=2 and file#=28 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=41 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=42 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=43 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=44 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=54 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=55 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=58 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=60 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=61 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=63 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=64 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=66 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=67 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=72 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=73 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=76 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=77 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=78 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=79 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=80 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=81 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=83 ;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=106;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=107;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=108;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=109;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=110;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=111;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=123;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=124;
update fet$ set length=524159 where ts#=13 and block#=2 and file#=125;

这样处理完后，这个表空间还有16万个碎片。

针对那些有数据的数据文件一样手工结合，跳过那些有数据的块。查找dba_extents，找出那个临时段所占用的块号和块数。

=====
4号文件

delete fet$ where ts#=13 and file#=4 and block# >2 and block# < 344181;
update fet$ set length=344183 where ts#=13 and file#=4 and block#=2;
delete fet$ where ts#=13 and file#=4 and block# > 344195;
update fet$ set length=167806 where ts#=13 and file#=4 and block#=344195;

=====
10号文件

delete fet$ where ts#=13 and file#=10 and block# >2 and block# < 288852;
update fet$ set length=288850 where ts#=13 and file#=10 and block#=2;
delete fet$ where ts#=13 and file#=10 and block# >2 and block# > 288867;
update fet$ set length=223134 where ts#=13 and file#=10 and block#=288867;

=====
13号文件

delete fet$ where ts#=13 and file#=13 and block# >2 and block# < 30298;
update fet$ set length=30296 where ts#=13 and file#=13 and block#=2;
delete fet$ where ts#=13 and file#=13 and block# >2 and block# > 30318;
update fet$ set length=481683 where ts#=13 and file#=13 and block#=30318;

=====
68号文件

delete fet$ where ts#=13 and file#=68 and block# >2 and block# < 518537;
update fet$ set length=518535 where ts#=13 and file#=68 and block#=2;

====
69号文件

delete fet$ where ts#=13 and file#=69 and block# >2 and block# < 182428;
update fet$ set length=182426 where ts#=13 and file#=69 and block#=2;
delete fet$ where ts#=13 and file#=69 and block# >2 and block# > 182438;
update fet$ set length=341723 where ts#=13 and file#=69 and block#=182438;

这样处理完后，直接drop tablespace，很快就搞定了。

编者注：您可以通过以下连接参与关于本文的讨论，直接和作者对话：

http://www.itpub.net/555223.html