|
背景:
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,很快就搞定了。 |
|