表的在线重定义

一张表含有主键test，本来是存放在段空间手动管理的表空间上的，在表逐渐变大以后并发量增大以后，性能开始变差，我们就使用在线重定义的方法来重建这张表，并把这张表改造成分区表

1、  准备实验环境

HR@PROD> selectsegment_name,tablespace_name from user_segments where tablespace_name in('MSSM','ASSM');

 

SEGMENT_NAME                             TABLESPACE_NAME

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

TEST                                     MSSM

 

HR@PROD> alter table test add constraintpk_test primary key (employee_id);

 

Table altered.

 

HR@PROD> alter index pk_test rebuildtablespace mssm online;

 

Index altered.

 

HR@PROD> select segment_name,tablespace_name from user_segments wheretablespace_name in ('MSSM','ASSM');

 

SEGMENT_NAME                             TABLESPACE_NAME

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

TEST                                     MSSM

PK_TEST                                  MSSM

2、  开始进行表的在线重定义

HR@PROD>execdbms_redefinition.can_redef_table('HR','TEST',dbms_redefinition.cons_use_pk);

 

PL/SQLprocedure successfully completed.

 

HR@PROD>create table test_redef     

  2 partition by hash(employee_id) partitions 4 tablespace assm

  3  asselect * from salary where 1=0;

 

Tablecreated.

 

HR@PROD>select table_name,partition_name from user_tab_partitions;

 

TABLE_NAME                     PARTITION_NAME

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

TEST_REDEF                     SYS_P25

TEST_REDEF                     SYS_P26

TEST_REDEF                     SYS_P27

TEST_REDEF                     SYS_P28

 

 

HR@PROD>exec DBMS_REDEFINITION.START_REDEF_TABLE ('HR','TEST','TEST_REDEF');

 

PL/SQLprocedure successfully completed.

 

HR@PROD>select count(*) from test_redef partition (SYS_P25);

 

  COUNT(*)

----------

        25

  1* select count(*) from test_redef partition(SYS_P26)

HR@PROD>/

 

  COUNT(*)

----------

        32

  1* select count(*) from test_redef partition(SYS_P27)

HR@PROD>/

 

  COUNT(*)

----------

        31

  1* select count(*) from test_redef partition(SYS_P28)

HR@PROD>/

 

  COUNT(*)

----------

        19

 

HR@PROD>select segment_name,tablespace_name from user_segments where tablespace_name in('MSSM','ASSM');

 

SEGMENT_NAME                             TABLESPACE_NAME

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

TEST                                     MSSM

PK_TEST                                  MSSM

TEST_REDEF                               ASSM

TEST_REDEF                               ASSM

TEST_REDEF                               ASSM

TEST_REDEF                               ASSM

 

  1 declare

  2 num_errors PLS_INTEGER;

  3 begin

  4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('HR','TEST','TEST_REDEF',dbms_redefinition.cons_orig_params,true,true,true,true,num_errors);

  5* end;

HR@PROD>/

 

PL/SQLprocedure successfully completed.

 

HR@PROD>select segment_name,tablespace_name from user_segments where tablespace_name in('MSSM','ASSM');

 

SEGMENT_NAME                             TABLESPACE_NAME

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

TEST                                     MSSM

PK_TEST                                  MSSM

TEST_REDEF                               ASSM

TEST_REDEF                               ASSM

TEST_REDEF                               ASSM

TEST_REDEF                               ASSM

TMP$$_PK_TEST0                           MSSM

 

7 rowsselected.

 

HR@PROD>exec DBMS_REDEFINITION.FINISH_REDEF_TABLE ('HR','TEST','TEST_REDEF');

 

PL/SQLprocedure successfully completed.

 

HR@PROD>  select segment_name,tablespace_name fromuser_segments where tablespace_name in ('MSSM','ASSM');

 

SEGMENT_NAME                             TABLESPACE_NAME

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

TMP$$_PK_TEST0                           MSSM

PK_TEST                                  MSSM

TEST_REDEF                               MSSM

TEST                                     ASSM

TEST                                     ASSM

TEST                                     ASSM

TEST                                     ASSM

这样就已经完成了表的在线重定义，但索引还是在原来的表空间上，这个时候我们就需要重建索引，把索引重建到自动管理的表空间上

HR@PROD>alter index pk_test rebuild tablespace assm online;

 

Indexaltered.

 

HR@PROD>select segment_name,tablespace_name from user_segments where tablespace_name in('MSSM','ASSM');

 

SEGMENT_NAME                             TABLESPACE_NAME

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

TMP$$_PK_TEST0                           MSSM

PK_TEST                                  ASSM

TEST_REDEF                               MSSM

TEST                                     ASSM

TEST                                     ASSM

TEST                                     ASSM

TEST                                     ASSM

 

任务完成