How to move a table and its indexes into another tablespace with simply commands? (DBMR2363)
From DBMaker5.3, DBMaker supports the command “ALTER TABLE table_name MOVE TABLESPACE tablespace_name” used to move tables and its indexes into another tablespace. Users can’t access the table’s data until the process of moving objects has been finished.
- If the table and its indexes are in the same tablespace, users can move them into another tablespace at the same time by using the command. In addition, the index also can be moved into another tablespace that is different the tablespace the table is moved into by rebuilding the index.
For example:
Create table t1 and its index idx1 in tablespace ts1, and then move both of them into tablespace ts2 first by using the command “alter table t1 move tablespace ts2”. Lastly, move index idx1 into tablespace ts3 by rebuilding it. Details are as follows.
dmSQL> create table t1(c1 int,c2 int) in ts1;
dmSQL> create index idx1 on (c1) in ts1;
dmSQL> def table t1;
create table SYSADM.T1 (C1 INTEGER default null, C2 INTEGER default null) in TS1 lock mode row fillfactor 100;
create index IDX1 on SYSADM.T1 (C1 asc) in TS1;
dmSQL> alter table t1 move tablespace ts2;
dmSQL> def table t1;
create table SYSADM.T1 (C1 INTEGER default null , C2 INTEGER default null) in TS2 lock mode row fillfactor 100 ;
create index IDX1 on SYSADM.T1 ( C1 asc ) in TS2;
dmSQL> rebuild index idx1 for t1 in ts3;
dmSQL> def table t1;
create table SYSADM.T1 (C1 INTEGER default null , C2 INTEGER default null) in TS2 lock mode row fillfactor 100 ;
create index IDX1 on SYSADM.T1 (C1 asc) in TS3;
- If the table and its indexes are in different tablespace, users can move the table into another tablespace first, and then move the index into the tablespace by rebuilding the index.
For example:
Create table t3 in tablespace ts1 and create its index idx3 in tablespace ts2. Next, move table t3 into ts3 by using the command “alter table t3 move tablespace ts3”. Lastly, move index idx1 into tablespace ts3 by rebuilding it. Details are as follows.
dmSQL> create table t3(c1 int,c2 int) in ts1;
dmSQL> create index idx3 on t3(c1) in ts2;
dmSQL> def table t3;
create table SYSADM.T3 (C1 INTEGER default null , C2 INTEGER default null) in TS1 lock mode row fillfactor 100 ;
create index IDX3 on SYSADM.T3 (C1 asc) in TS2;
dmSQL> alter table t3 move tablespace ts3;
dmSQL> def table t3;
create table SYSADM.T3 (C1 INTEGER default null , C2 INTEGER default null) in TS3 lock mode row fillfactor 100;
create index IDX3 on SYSADM.T3 (C1 asc) in TS2;
dmSQL> rebuild index idx3 for t3 in ts3;
dmSQL> def table t3;
create table SYSADM.T3 (C1 INTEGER default null , C2 INTEGER default null)in TS3 lock mode row fillfactor 100;
create index IDX3 on SYSADM.T3 (C1 asc) in TS3;
Before DBMaker5.3, users can follow the following steps to move a table and its indexes into another tablespace.
- Unload the table to an external text file.
UNLOAD TABLE FROM table_name TO file_name
2. Open the text file “file_name” and manually modify the tablespace name.
alter table SYSADM.TEST primary key (C1) in old_TS1;
==> alter table SYSADM.TEST primary key (C1) in new_TS1;
create index intest_SYSADM.TEST on SYSADM.TEST (C2 asc, C3 asc) in old_TS1
==> create index intest_SYSADM.TEST on SYSADM.TEST (C2 asc, C3 asc) in new_TS1
3. Drop the table “table_name” and then load contents of the table from the text file “file_name”.
LOAD TABLE FROM file_name
Version: DBMaker 4.x/5.x
Product: Normal/Bundle
Platform: Windows/Linux