修改fet$基表，结合碎片

logzgh

背景：

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，很快就搞定了。

biti_rainy

胆大心细

caoliwei

最初由 logzgh 发布
[B]背景：

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，很快就搞定了。 [/B]

这都敢做 ^_^ 哈哈

monkeyai

8i以后不用这么干了。看楼主玩的真玄。

fly_bug

真是胆大包天啊！！！
一不小心会死的很惨！！！！！！！！

SONGHEFEI

这也敢做？？还是生产环境!

logzgh

呵呵。。。我最开始想到这个想法时，也是打算在晚上做，并做好各种保证的方法，以做好回退之路。
但是随着对oracle结合碎片过程和fet$表的了解，我就越来越有信心这样做了。我先将tb_cdr表空间offline后，这样可以保证关于该表空间在fet$表中的记录不会修改。
然后我再备份关于该表空间的记录。然后再做修改。
因为我并没有修改其他的记录。所以不会影响其他的表空间的记录。

这也是被客户逼的没办法，再不干掉这个表空间，就无法出帐了，到时还得了啊。

logzgh

我觉的就是如biti所说，只要胆大心细就可以做了。

zjusxzj

有过很你同样的问题！后来我是起了个别job　一点一点resize

logzgh

最初由 zjusxzj 发布
[B]有过很你同样的问题！后来我是起了个别job　一点一点resize [/B]

但是resize有个问题，在那里一点一点的resize会影响业务的吧。
即使每次Resize一点，也要好长时间。