基于主键的在线重定义表的三种索引重建方式

环境配置：创建mssm和assm表空间，在mssm上创建带有主键的表t,在assm上创建中间表t_interim。验证t可以使用基于主键的在线重定义，启动在线重定义。这些步骤完成后，进入到本文讨论的三种索引重建方式。

步骤：1 创建mssm，assm

LEO1@LEO1>create tablespace MSSM datafile '/u01/app/oracle/oradata/LEO1/disk1/mssm01.dbf' size 20m extent management local segment space management manual;

Tablespace created.

LEO1@LEO1>create tablespace ASSM datafile '/u01/app/oracle/oradata/LEO1/disk1/assm01.dbf' size 20m extent management local segment space management auto;

Tablespace created.

2.检查表空间的段空间管理模式

LEO1@LEO1>select segment_space_management,tablespace_name from dba_tablespaces where tablespace_name in ('MSSM','ASSM');

SEGMEN TABLESPACE_NAME

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

AUTO ASSM

MANUAL MSSM

3.在MSSM表空间上创建t表

LEO1@LEO1>create table t(a number constraint pk_t primary key ) tablespace MSSM;

Table created.

LEO1@LEO1>insert into t values (10);

1 row created.

LEO1@LEO1>insert into t values (20);

1 row created.

LEO1@LEO1>insert into t values (30);

1 row created.

LEO1@LEO1>insert into t values (40);

1 row created.

LEO1@LEO1>insert into t values (50);

1 row created.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select * from t;

A

----------

10

20

30

40

50

LEO1@LEO1>select table_name,tablespace_name from user_tables where table_name='T';

TABLE_NAME TABLESPACE_NAME

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

T MSSM

4.验证是否满足基于主键在线重定义要求

LEO1@LEO1>exec dbms_redefinition.can_redef_table ('LEO1','t',dbms_redefinition.cons_use_pk);

PL/SQL procedure successfully completed.

5.创建在线重定义中间表

LEO1@LEO1>create table t_interim (a number) tablespace assm;

Table created.

6.查看t表和t_interim表所在的表空间

LEO1@LEO1>select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM');

TABLE_NAME TABLESPACE_NAME

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

T MSSM

T_INTERIM ASSM

7.启动在线重定义

LEO1@LEO1>exec dbms_redefinition.start_redef_table('leo1','t','t_interim'); PL/SQL procedure successfully completed.

LEO1@LEO1>select * from t_interim;

A

----------

10

20

30

40

50

这些步骤完成后，进入到本文讨论的三种索引重建方式。

方法一：

手工同步，完成在线重定义。删掉中间表，在t表上增加约束pk_t

LEO1@LEO1>exec dbms_redefinition.sync_interim_table('leo1','t','t_interim');

PL/SQL procedure successfully completed.

LEO1@LEO1>exec dbms_redefinition.finish_redef_table('leo1','t','t_interim');

PL/SQL procedure successfully completed.

LEO1@LEO1>select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM');

TABLE_NAME TABLESPACE_NAME

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

T ASSM

T_INTERIM MSSM

LEO1@LEO1>select index_name,table_name,tablespace_name,status from