整库占用空间过大，无法磁带备份
整库占用空间过大，无法磁带备份，查看2000张分表extent过大，总共40G，但每张表里只有几百条记录，EXTENT SIZE 4096 NEXT SIZE 4096，这个无法备份是由extent过大造成的么？除了重建表或扩磁带有什么其他方法可以备份数据? 查了一下ibm支持，可以这样解决如何重整数据库表空间使之运行效率更高
问题：
如果数据库表拥有过多的extent，将会带来以下问题：
1、影响访问该表的效率。当数据存储过于分散时，数据的访问使用将是低效的。
2、达到数据存储的上限，导致数据将不能被插入到数据库表中。
因此，我们在以下情况下需要该功能：
1、重整那些拥有过多extent的数据库表，并释放部分空间
2、从一个大表中删除大部分数据
3、改变一个大表的结构
答案：
1、在IDS 7.31中您可以使用oncheck -me来实现
2、在IDS 9.x 或 IDS 10.x，您可以通过：
2.1 创建一个与源表同结构的raw表
2.2 使用"insert into ... select * from ..."语句来传输数据
2.3 改变表的模式为正常模式，重命名或删除原表，然后将新表更名为原表名
2.4 重新创建所需的索引或约束
例如，我们需重构customer表，该表拥有一个索引和一个主键:
create raw table customer_new
(
customer_num serial not null ,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18)
) extent size 16 next size 16 lock mode page;
insert into customer_new select * from customer;
alter table customer_new type (standard);
drop table customer;
rename table customer_new to customer;
set pdqpriority high;
alter table customer add constraint primary key (customer_num) constraint pk_customer;
create index zip_ix on customer (zipcode) ;
3、在IDS 11.5上我们可以利用repack和shrink命令来重构表
例如，我们需对表customer_new进行重构，并释放空间
3.1 重构前
oncheck -pt stores:customer_new
TBLspace Report for stores:informix.customer_new
Physical Address 2:536
Creation date 11/11/2009 12:11:21
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 40
Current serial value 962567
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 32
Number of pages allocated 3776
Number of pages used 3761
Number of data pages 3760
Number of rows 52632
Partition partnum 2097237
Partition lockid 2097237
Extents
Logical Page Physical Page Size Physical Pages
0 2:1438 8 8
8 2:1454 152 152
160 2:1610 40 40
200 2:1654 48 48
248 2:1706 40 40
288 2:1750 48 48
336 2:1802 40 40
376 2:1846 48 48
424 2:1898 40 40
464 2:1942 48 48
512 2:1994 40 40
552 2:2038 48 48
600 2:2090 40 40
640 2:2134 48 48
688 2:2190 88 88
776 2:2286 88 88
864 2:2382 96 96
960 2:2486 80 80
1040 2:2574 96 96
1136 2:2678 80 80
1216 2:2766 96 96
1312 2:2870 80 80
1392 2:2958 96 96
1488 2:3062 80 80
1568 2:3150 96 96
1664 2:3254 80 80
1744 2:3342 64 64
1808 2:3414 96 96
1904 2:3518 80 80
1984 2:3606 96 96
2080 2:3718 176 176
2256 2:3910 176 176
2432 2:4102 192 192
2624 2:4310 160 160
2784 2:4486 160 160
2944 2:4662 192 192
3136 2:4870 160 160
3296 2:5046 192 192
3488 2:5254 160 160
3648 2:5430 128 128
该表共分配3776个数据页，拥有52632条记录
3.2 删除部分数据
dbaccess stores <<!
delete from customer_new where customer_num > 1000;
!
Database selected.
52590 row(s) deleted.
Database closed.
oncheck -pt stores:customer_new
TBLspace Report for stores:informix.customer_new
Physical Address 2:536
Creation date 11/11/2009 12:11:21
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 40
Current serial value 962567
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 32
Number of pages allocated 3776
Number of pages used 3761
Number of data pages 3
Number of rows 42
Partition partnum 2097237
Partition lockid 2097237
Extents
Logical Page Physical Page Size Physical Pages
0 2:1438 8 8
8 2:1454 152 152
160 2:1610 40 40
200 2:1654 48 48
248 2:1706 40 40
288 2:1750 48 48
336 2:1802 40 40
376 2:1846 48 48
424 2:1898 40 40
464 2:1942 48 48
512 2:1994 40 40
552 2:2038 48 48
600 2:2090 40 40
640 2:2134 48 48
688 2:2190 88 88
776 2:2286 88 88
864 2:2382 96 96
960 2:2486 80 80
1040 2:2574 96 96
1136 2:2678 80 80
1216 2:2766 96 96
1312 2:2870 80 80
1392 2:2958 96 96
1488 2:3062 80 80
1568 2:3150 96 96
1664 2:3254 80 80
1744 2:3342 64 64
1808 2:3414 96 96
1904 2:3518 80 80
1984 2:3606 96 96
2080 2:3718 176 176
2256 2:3910 176 176
2432 2:4102 192 192
2624 2:4310 160 160
2784 2:4486 160 160
2944 2:4662 192 192
3136 2:4870 160 160
3296 2:5046 192 192
3488 2:5254 160 160
3648 2:5430 128 128
我们可以看到，虽然数据已经被删除，但其占据的空间并没有被释放
3.3 使用Repack 和 Shrink命令
dbaccess sysadmin <<!
> execute function task("table repack shrink", "customer_new", "stores");
> !
Database selected.
(expression) Succeeded: table repack shrink stores:informix.customer_new
1 row(s) retrieved.
Database closed.
3.4 再检查
oncheck -pt stores:customer_new
TBLspace Report for stores:informix.customer_new
Physical Address 2:536
Creation date 11/11/2009 12:11:21
TBLspace Flags 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 962567
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 32
Number of pages allocated 8
Number of pages used 8
Number of data pages 3
Number of rows 42
Partition partnum 2097237
Partition lockid 2097237
Extents
Logical Page Physical Page Size Physical Pages
0 2:1438 8 8
这时我们可以发现，已经删除数据的空间被释放出来了，表的extent个数也降下来了。
页:
[1]