将表迁移到其他的表空间

一、普通表和索引：

1、转移表

alter table table_name  move tablespace  tablespace_name;

2、转移索引

alter index index_name rebuild tablespace tablespace_name;

二、含有lob字段的表和索引：

1、转移表

alter table table_name t move tablespace tablespace_name;

2、转移索引

alter index index_name rebuild tablespace tablespace_name;

3、转移lob字段

alter table table_name move lob (column_name_01) store as (tablespace tablespace_name); .

.......................................

alter table table_name move lob (column_name_0n) store as (tablespace tablespace_name);

注：表中有多个字段的要逐个转移。

三、分区表和索引：

1、分区表的分区要一个分区一个分区的转移

alter table table_name move partition partition01_name tablespace tablespace_name;

.......................................

alter table table_name move partition partition0n_name tablespace tablespace_name;

2、分区表的本地索引，要一个分区一个分区的转移

alter index index_name rebuild partition partition01_name tablespace tablespace_name;

 ........................................

alter index index_name rebuild partition partition0n_name tablespace tablespace_name;

注：分区表的本地索引在各个分区上的索引名字相同。

3、分区表全局索引

alter index index_name rebuild tablespace tablespace_name;

四、测试过程

创建测试表省略！！！

SQL> col segment_name format a30;

SQL> col tablespace_name format a20;

1、测试普通表

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME TABLESPACE

------------------------------ ----------

 T1                TEST01

IN_T1           TEST01

2 rows selected.

SQL> alter table t1 move tablespace test02;

Table altered.

SQL> alter index in_t1 rebuild tablespace test02;

Index altered.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NA TABLESPACE

 ----------                  ----------

T1                          TEST02

IN_T1                    TEST02

2、测试含lob字段的表

SQL> create table t2(a integer,b blob) tablespace test01;

Table created.

SQL> create index in_t2 on t2(a) tablespace test01;

Index created.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME                                   TABLESPACE

------------------------------                             ----------

T1                                                                  TEST02

IN_T1                                                            TEST02

T2                                                                  TEST01

SYS_IL0000052527C00002$$               TEST01

SYS_LOB0000052527C00002$$           TEST01

IN_T2 TEST01 6 rows selected.

SQL> alter table t2 move tablespace test02;

Table altered.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME                                  TABLESPACE

 ------------------------------                           ----------

T1                                                               TEST02

IN_T1                                                         TEST02

T2                                                               TEST02

SYS_IL0000052527C00002$$            TEST01

SYS_LOB0000052527C00002$$        TEST01

IN_T2                                                           TEST01

6 rows selected.

SQL> alter table t2 move lob (b) store as (tablespace test02);

Table altered.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME                        TABLESPACE

------------------------------                 ----------

T1                                                     TEST02

IN_T1                                                 TEST02

T2                                                      TEST02

SYS_IL0000052527C00002$$   TEST02

SYS_LOB0000052527C00002$$ TEST02

IN_T2                                                   TEST01

6 rows selected.

SQL> alter index in_t2 rebuild tablespace test02;

Index altered.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME                        TABLESPACE

 ------------------------------                  ----------

T1                                                      TEST02

IN_T1                                                TEST02

T2                                                       TEST02

SYS_IL0000052527C00002$$    TEST02

SYS_LOB0000052527C00002$$  TEST02

IN_T2                                                     TEST02

6 rows selected.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME                TABLESPACE

 ------------------------------         ----------

T1                                              TEST02

IN_T1                                         TEST02

T2                                               TEST02

SYS_IL0000052527C00002$$ TEST02

SYS_LOB0000052527C00002$$ TEST02

IN_T2                                           TEST02

DBOBJS                                      TEST02

DBOBJS                                      TEST02

DBOBJS_IDX TEST02

DBOBJS_IDX TEST02

DBOBJS_IDX02 TEST02

11 rows selected.

3、测试分区表

SQL> CREATE TABLE dbobjs (OBJECT_ID NUMBER NOT NULL, OBJECT_NAME varchar2(128), CREATED DATE NOT NULL) PARTITION BY RANGE (CREATED) (PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')), PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')));

SQL> CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL (PARTITION dbobjs_06 TABLESPACE test01, PARTITION dbobjs_07 TABLESPACE test01);

SQL> create index dbobjs_idx02 on dbobjs(OBJECT_NAME) tablespace test01;

SQL> alter table dbobjs move partition dbobjs_06 tablespace test02;

Index altered.

SQL> alter table dbobjs move partition dbobjs_07 tablespace test02;

Index altered.

SQL> alter index dbobjs_idx rebuild partition dbobjs_06 tablespace test02;

Index altered.

SQL> alter index dbobjs_idx rebuild partition dbobjs_07 tablespace test02;

Index altered.

SQL> alter index dbobjs_idx02 rebuild tablespace test02;

Index altered.

SQL> select segment_name,tablespace_name from dba_segments where wner='TEST';

SEGMENT_NAME                             TABLESPACE

------------------------------                       ----------

T1                                                         TEST02

IN_T1                                                   TEST02

T2                                                          TEST02

SYS_IL0000052527C00002$$       TEST02

SYS_LOB0000052527C00002$$   TEST02

IN_T2                                                     TEST02

DBOBJS                                                 TEST02

DBOBJS                                                 TEST02

DBOBJS_IDX                                          TEST02

DBOBJS_IDX                                          TEST02

DBOBJS_IDX02                                       TEST02

11 rows selected.

至此，所有的数据对象都可以迁移成功。