ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OCM实验-使用在线重定义方式迁移表

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

原创 Linux操作系统 作者:super_sky 时间:2014-02-16 23:15:39 0 删除 编辑

要求使用在线重定义方式迁移表
优点:支持在线读/写,不影响大查询,对海量数据的表进行操作效率非常好,实质只更新数据字典,不移动数据
缺点:在线重定义后表上的主键、索引不会同步过来,必须重建,只变换表名.在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/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2011-01-11

  • 博文量
    77
  • 访问量
    758067