ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表在同一库中不同表空间上转移

表在同一库中不同表空间上转移

原创 Linux操作系统 作者:听海★蓝心梦 时间:2009-09-11 10:57:31 0 删除 编辑

      今天开发说有一个表空间有300G,想要把这个表空间上的对象清理下,等表空间使用降低时,迁移到本库的另外一个小的表空间上。然后删除旧的表空间和其数据文件,释放物理磁盘空间。

        因为是在同一个库上,而且迁移的两个表空间名字不同,所以不能使用oracle的exp/imp来完成(因为exp/imp传输表空间,要求表空间名字相同),也不能使用rman copy datafiles到其他地方,因为这样不能缩小datafiles的大小。所以采用以下方法处理:表迁移,从一个表空间迁移到另外一个表空间:

一、普通表和索引:

1、转移表

alter table table_name t 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.

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/751371/viewspace-614425/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    256
  • 访问量
    1192428