ITPUB论坛－专业的IT技术社区

登录 注册
ITPUB论坛－专业的IT技术社区»论坛 › ≡ 大数据与数据库 ≡ › Oracle数据库管理 › 修改fet$基表，结合碎片
1234下一页
返回列表 发新帖
查看: 15934|回复: 33

[精华] 修改fet$基表，结合碎片

[复制链接]
logzgh
论坛徽章:
0
发表于 2006-5-24 17:31 | 显示全部楼层 |阅读模式
背景：

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，很快就搞定了。
数据, 字典, 空间, 客户, local

使用道具 举报

回复
biti_rainy
论坛徽章:
86
ITPUB元老 日期:2005-02-28 12:57:002012新春纪念徽章 日期:2012-01-04 11:49:542012新春纪念徽章 日期:2012-02-13 15:13:202012新春纪念徽章 日期:2012-02-13 15:13:202012新春纪念徽章 日期:2012-02-13 15:13:202012新春纪念徽章 日期:2012-02-13 15:13:202012新春纪念徽章 日期:2012-02-13 15:13:20咸鸭蛋 日期:2012-05-08 10:27:19版主8段 日期:2012-05-15 15:24:112013年新春福章 日期:2013-02-25 14:51:24
发表于 2006-5-24 17:45 | 显示全部楼层
胆大心细

使用道具 举报

回复
caoliwei
论坛徽章:
15
祖国60周年纪念徽章 日期:2009-10-09 08:28:002009架构师大会纪念徽章 日期:2010-01-18 13:43:21ITPUB十周年纪念徽章 日期:2011-11-01 16:20:28
发表于 2006-5-24 18:08 | 显示全部楼层

Re: 修改fet$基表，结合碎片

最初由 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
论坛徽章:
11
授权会员 日期:2006-05-12 15:15:27数据库板块每日发贴之星 日期:2006-05-19 01:01:35ITPUB元老 日期:2006-05-19 08:58:40操作系统板块每日发贴之星 日期:2006-06-05 01:01:57会员2007贡献徽章 日期:2007-09-26 18:42:10ITPUB十周年纪念徽章 日期:2011-11-01 16:19:412014系统架构师大会纪念章 日期:2014-08-28 15:15:37技术图书徽章 日期:2014-09-09 11:11:10
发表于 2006-5-24 18:24 | 显示全部楼层
8i以后不用这么干了。看楼主玩的真玄。

使用道具 举报

回复
fly_bug
论坛徽章:
2
2008新春纪念徽章 日期:2008-02-13 12:43:032010新春纪念徽章 日期:2010-03-01 11:08:24
发表于 2006-5-24 19:41 | 显示全部楼层
真是胆大包天啊！！！
一不小心会死的很惨！！！！！！！！

使用道具 举报

回复
SONGHEFEI
论坛徽章:
1
ITPUB新首页上线纪念徽章 日期:2007-10-20 08:38:44
发表于 2006-5-24 20:16 | 显示全部楼层
这也敢做？？还是生产环境!

使用道具 举报

回复
logzgh
论坛徽章:
0
 楼主| 发表于 2006-5-25 10:16 | 显示全部楼层
呵呵。。。我最开始想到这个想法时，也是打算在晚上做，并做好各种保证的方法，以做好回退之路。
但是随着对oracle结合碎片过程和fet$表的了解，我就越来越有信心这样做了。我先将tb_cdr表空间offline后，这样可以保证关于该表空间在fet$表中的记录不会修改。
然后我再备份关于该表空间的记录。然后再做修改。
因为我并没有修改其他的记录。所以不会影响其他的表空间的记录。

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

使用道具 举报

回复
logzgh
论坛徽章:
0
 楼主| 发表于 2006-5-25 11:00 | 显示全部楼层
我觉的就是如biti所说，只要胆大心细就可以做了。

使用道具 举报

回复
zjusxzj
论坛徽章:
5
授权会员 日期:2005-10-30 17:05:33ITPUB元老 日期:2005-11-01 10:17:33铁扇公主 日期:2006-04-13 11:43:07会员2006贡献徽章 日期:2006-04-17 13:46:34生肖徽章2007版:鼠 日期:2008-01-02 17:35:53
发表于 2006-5-25 12:50 | 显示全部楼层
有过很你同样的问题！后来我是起了个别job　一点一点resize

使用道具 举报

回复
logzgh
论坛徽章:
0
 楼主| 发表于 2006-5-25 14:03 | 显示全部楼层
最初由 zjusxzj 发布
[B]有过很你同样的问题！后来我是起了个别job　一点一点resize [/B]


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

使用道具 举报

回复

1234下一页
返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　联系我们 未成年人举报专区　
京ICP备16024965号-8 　北京市公安局海淀分局网监中心备案编号：11010802021510　广播电视节目制作经营许可证：编号（京）字第1149号   
快速回复 返回顶部 返回列表