informix数据库表空间扩展
这里说的扩展,是说表空间的时间范围扩大,不是给表空间增加chunk文件。
1、alter fragment on table table_name detach datadbs01 table_name_datadbs01 ; 相当于先把该表空间卸载。数据不会丢失,会自动生成一个相同表结构的表,表名就是你指定的able_name_datadbs01,并把数据备份到该表中。
2、alter fragment on table table_name add
((scan_start_time < datetime(2013-10-31 00:00:00) year to second ) AND
(scan_start_time >= datetime(2013-05-31 00:00:00) year to second ) ) in datadbs01; 根据实际修改时间、表空间
3、数据恢复,insert into table_name select * from table_name_datadbs01;
1、ta_c_tr表最大分分区是14年6月30日
查看ta_c_tr表分区信息如下
create table ta_c_tr (
int_id INT8 not null,
scan_start_time DATETIME YEAR TO SECOND
)
fragment by expression ((scan_start_time < datetime(2014-3-31 00:00:00) year to second ) AND (scan_start_time >= datetime(2014-01-01 00:00:00) year to second ) ) in datadbs1,
((scan_start_time < datetime(2014-06-30 00:00:00) year to second ) AND (scan_start_time >= datetime(2014-3-31 00:00:00) year to second ) ) in datadbs2
extent size 1024 next size 1024
lock mode row;
create unique index idx_ta_c_tr on ta_c_tr
( scan_start_time,
int_id
);
insert into ta_c_tr values (3,'2014-09-10 00:00:00') ;
SQL Error (-271): Could not insert new row into the table.
Record/Key doesn't qualify for any table/index fragment.
数据和现有分区不匹配,无法插入
2、需要扩展表空间到9月30号
a)alter fragment on table ta_c_tr detach datadbs2 ta_c_tr_datadbs2; 先把datadbs2表空间卸载,该表空间的数据会备份到 ta_c_tr_datadbs2表中
select * from ta_c_tr_datadbs2;可以查看数据
b)alter fragment on table ta_c_tr add
((scan_start_time < datetime(2014-09-30 00:00:00) year to second ) AND
(scan_start_time >= datetime(2014-03-31 00:00:00) year to second ) ) in datadbs02; 修改时间到9月30号
3、数据恢复
insert into ta_c_tr select * from ta_c_tr_datadbs2; 把备份的数据恢复回去。
4、插入数据
insert into ta_c_tr values (3,'2014-09-10 00:00:00') ;
Rows inserted: 1.