测试将手动段管理表空间上的表通过在线重定义迁移至自动段管理表空间
1、创建手动段管理表空间，自动段管理表空间
SYS@jzh>create tablespace MSSM datafile '/u01/app/oracle/oradata/jzh/mssm01.dbf' size 10M segment space management manual;

SYS@jzh>create tablespace ASSM datafile '/u01/app/oracle/oradata/jzh/assm01.dbf' size 10M segment space management auto;
2、在手动段管理表空间上创建测试表
JZH@jzh>create table t (x number constraint pk_t primary key) tablespace mssm;

Table created.

JZH@jzh>select segment_name,segment_type,tablespace_name from user_segments where tablespace_name in ('MSSM','ASSM');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
-------------------- ------------------ ------------------------------
T TABLE MSSM
PK_T INDEX MSSM
3、创建中间表
JZH@jzh>create table t_ref tablespace assm as select * from t where 1=0;

Table created.
4、验证是否满足在线重定义条件
JZH@jzh>BEGIN
2 DBMS_REDEFINITION.CAN_REDEF_TABLE('jzh','T',
3 DBMS_REDEFINITION.CONS_USE_PK);
4 END;
5 /

PL/SQL procedure successfully completed.

5、开始在线重定义
JZH@jzh>BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('jzh', 't','t_ref',
3 null,dbms_redefinition.cons_use_pk);
4 END;
5 /

PL/SQL procedure successfully completed.
6、COPY约束
JZH@jzh>DECLARE
2 num_errors PLS_INTEGER;
3 BEGIN
4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('jzh', 't','t_ref',
5 DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
6 END;
7 /

PL/SQL procedure successfully completed.
7、将中间表生成的索引rebuild至自动段管理的表空间
JZH@jzh>alter index TMP$$_PK_T0 rebuild tablespace assm;

Index altered.
8、同步数据
JZH@jzh>BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('jzh', 't', 't_ref');
3 END;
4 /

PL/SQL procedure successfully completed.
9、结束在线重定义
JZH@jzh>BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('jzh', 't', 't_ref');
3 END;
4 /

PL/SQL procedure successfully completed.
10、查询结果
JZH@jzh>select segment_name,segment_type,tablespace_name from user_segments where tablespace_name in ('MSSM','ASSM');

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------   ------------------ ------------------------------
T_REF    TABLE MSSM
T TABLE ASSM
TMP$$_PK_T0 INDEX MSSM
PK_T   INDEX ASSM

表T和索引PK_T已经迁移至assm表空间了！

ITPub博客

测试在线重定义

最新文章