OCM实验-使用在线重定义方式迁移表

最新推荐文章于 2019-03-11 13:09:40 发布

cuandan3585

最新推荐文章于 2019-03-11 13:09:40 发布

阅读量184

点赞数

要求使用在线重定义方式迁移表
优点：支持在线读/写，不影响大查询，对海量数据的表进行操作效率非常好，实质只更新数据字典，不移动数据
缺点：在线重定义后表上的主键、索引不会同步过来，必须重建，只变换表名.在finish转换过程中原表是锁定状态
官方文档： PL/SQL Packages and Types Reference -> 搜索在线重定义dbms_redefinition

1.创建环境
SYS@testdb>create tablespace mssm datafile '/oracle/ora10g/oradata/mssm01.dbf' size 20M extent management local segment space management manual;
Tablespace created.
SYS@testdb>create tablespace assm datafile '/oracle/ora10g/oradata/assm01.dbf' size 20m extent management local segment space management auto;
Tablespace created.

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

SEGMEN TABLESPACE_NAME
------ ------------------------------
AUTO ASSM
MANUAL MSSM

在mssm表空间上，创建测试表t，并插入测试数据
LEO1@testdb>create table t (id int constraint pk_t primary key) tablespace mssm;
Table created.
LEO1@testdb>insert into t values(1);
1 row created.
LEO1@testdb>insert into t values(2);
1 row created.
LEO1@testdb>insert into t values(3);
1 row created.
LEO1@testdb>commit;
Commit complete.
LEO1@testdb>select * from t;
        ID
----------
         1
         2
         3
LEO1@testdb>
LEO1@testdb>select table_name,tablespace_name from user_tables;

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
T MSSM

2.使用在线重定义方式将t表从mssm表空间迁移到assm表空间
1）验证是否可以基于主键方式迁移
LEO1@testdb>exec dbms_redefinition.can_redef_table(uname=>'leo1',tname=>'t',options_flag=>dbms_redefinition.cons_use_pk);
PL/SQL procedure successfully completed.
2）创建在线重定义中间表
LEO1@testdb>create table t_interim (id int) tablespace assm;
Table created.
LEO1@testdb>select table_name,tablespace_name from user_tables where table_name in ('T','T_INTERIM');

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T                              MSSM
T_INTERIM                      ASSM
3）在线重定义表
LEO1@testdb>exec dbms_redefinition.start_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
PL/SQL procedure successfully completed.

LEO1@testdb>select * from t;

        ID
----------
         1
         2
         3

LEO1@testdb>select * from t_interim;

        ID
----------
         1
         2
         3
手动同步
LEO1@testdb>exec dbms_redefinition.sync_interim_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
PL/SQL procedure successfully completed.

完成在线重定义
******************************************************************
我在这之前做了一个drop 表的动作，证明在迁移过程中，源表是可以删除的。
LEO1@testdb>drop table t_interim;
drop table t_interim
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "LEO1"."T_INTERIM"

LEO1@testdb>drop table t;

Table dropped.

LEO1@testdb>
LEO1@testdb>select table_name from user_tables;

TABLE_NAME
------------------------------
T_INTERIM

这时，完成在线迁移将无法进行
LEO1@testdb>exec dbms_redefinition.finish_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
BEGIN dbms_redefinition.finish_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim'); END;

*
ERROR at line 1:
ORA-23540: Redefinition not defined or initiated
ORA-06512: at "SYS.DBMS_REDEFINITION", line 76
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1377
ORA-06512: at line 1
****************************************************8
重做上面的操作后，可以完成在线重定义操作了

LEO1@testdb>exec dbms_redefinition.finish_redef_table(uname=>'leo1',orig_table=>'t',int_table=>'t_interim');
PL/SQL procedure successfully completed.

检查表所在的表空间。
LEO1@testdb>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@testdb>select index_name,table_name,tablespace_name,status from user_indexes where index_name='PK_T';

INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_T T_INTERIM MSSM VALID

LEO1@testdb>alter index pk_t rebuild tablespace assm online;

Index altered.

LEO1@testdb>select index_name,table_name,tablespace_name,status from user_indexes where index_name='PK_T';

INDEX_NAME TABLE_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
PK_T T_INTERIM ASSM VALID

额，pk_t主键在中间表t_interim上。因此不能使用rebuild重建

需要给t表新建主键。
LEO1@testdb>alter table t add constraint pk_t_id primary key(id);
Table altered.

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

转载于:http://blog.itpub.net/11590946/viewspace-1082278/