将表迁移到其他的表空间

来源：互联网发布：sqlserver数据库连接编辑：程序博客网时间：2024/05/16 18:05

一、普通表和索引：

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 tablespacetablespace_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_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_IDX TEST02

DBOBJS_IDX02 TEST02

11 rows selected.

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