ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 使用online redefinition

使用online redefinition

原创 Linux操作系统 作者:janeting 时间:2009-07-20 17:01:30 0 删除 编辑
在线重定义普通表到分区表的过程

在线重定义表结构的优点:
1. 可以重新组合表结构,压缩表的空间.
2. 在线把表迁移到另外一个表空间中.
3. 把表从普通表到分区表之间互换.
4. 在线删除表的字段.
5. 修改表的参数,如initrans. 此参数在建立表的时候定义.以后不可修改,除非使用
online redefinition 重组表结构.
在线重定义的方式:
1. 使用主键(中间表上面创建,并且可以是没有NULL的唯一约束) 10g之前使用.
2. 使用rowid (10g以上版本,且该模式会创建一个隐式字段M_ROW$$,需要在重定义完成后手工删除该字段.10gR2以上会自动使用set unused)
主要步骤:
1. 使用dbms_redefinition.can_redef_table 检查表是否可以在线重定义.
2. 创建一个类似原来表的空的表结构.也叫中间表.(若重定义的对象是一个非常大的表,可以尝试以下方式,以提高速度.
Alter session force parallel dml parallel ;
Alter session force parallel query parallel ;
3. 使用dbms_redefinition.start_redef_table procedure 定义结构开始,参数如下:
用户名,原表名,中间表名.同步列的名称(可以是添加列或者是删除列)如:
Exec dbms_redefinition.start_redef_table(‘howey’,’int_howey’,’id id,name name ,job job ,deptno+10 deptno ,0 bonus’);
这个过程会执行以下两个步骤 :
1.把原来表中的数据全部插入中间表中,此插入过程并非使用insert命令插入,Oracle使用了类似loader的方式来优化插入速度.
2.创建mlog$_tablename 的快照日志表(临时存放在操作过程中原表中数据改变的log.)和rupd$_tablename (在mv刷新时用到).
4. 在中间表上面创建主键,原来表上面的constraint ,index ,trigger 也要都复制到中间表上面(2种方式:1手工 .2 自动:使用copy_table_dependents过程即可自动拷贝这些结构) 另外,原表的权限也会复制到中间表上面) 主要保证了中间表切换为新表后,可以立即被正常使用.不用其它的准备工作.
附:copy_table_dependents使用如下:
SQL> declare
   n number ;
   begin
   dbms_redefinition.copy_table_dependents('howey','test','int_test',1,true,true,true,true,n,true);
   end;
    /
PL/SQL procedure successfully completed.

5. 使用 dbms_redefinition.sync_interim_table 刷新日志表中的数据.这一步不是必须的.目的是使中间表和原表中的数据同步.主要防止在实施过程中原表数据更新频繁. 导致后续finish过程切换占用大量时间,从而影响系统正常使用.
6. 使用 dbms_redefinition.finish_redef_table 完成表的最终定义.
该过程会有以下2个步骤:
1, 应用日志表中的日志到中间表中,使中间表中的数据在此刻完全等同于原表数据.
2, 原表和中间表的表名作互换.实际就是修改了涉及这两个表的数据字典,若使用rowid方式,10gR1之前需要删除M_ROW$$字段或者设为unused. 10gR2之后就可以完全oracle自动处理了.
3. 删除mlog$_test和rupd$_test表.
7. 如果重定义失败,使用 dbms_redefinition.abort_redef_table 终止重定义过程.此操作会取消重定义中创建的日志表

使用10g新特性联机重定义表把普通表重定义为分区表,并且重定义后的分区表增加一个字段,以下为详细的执行步骤.
准备工作:
1.   创建一个单独的用户来做以下测试:
create user howey identified by h default tablespace users;
grant connect,resource to howey;

2. 创建一个测试用的普通表
SQL> create table test (a int,b int ,c varchar2(20));
Table created.

在原表中插入1000条数据:
SQL> declare i integer;
  2  begin
  3  for i in 1..1000 loop
  4  insert into  test values(i,1000-i,'test table');
  5  end loop;
  6  end;
  7  /
PL/SQL procedure successfully completed./

3.  创建主键,并创建测试用的trigger.,赋予该表权限.主要为了观察重定义前后的变化.
SQL> alter table test add constraint pk_a_test primary key (a);
Table altered.
SQL> create trigger tri_test
  2  before insert or delete on test for each row
  3  begin null;
  4  end;
  5  /
赋予该表权限.
SQL> grant select on test to system;
Grant succeeded.
SQL> grant insert on test to system;
Grant succeeded.


开始重定义表:
1. 检查该表是否可以重定义(在这里我临时把DBA的权限给了howey,测试完后收回. 或者使用system用户执行dbms包)
SQL> exec dbms_redefinition.can_REDEF_TABLE('howey','test');
PL/SQL procedure successfully completed.

2. 创建重新定义的中间表
创建一个分区表,将会把原来所有的数据在线转移到此表中:
SQL> create table int_test (a int, b int ,c varchar2(20),d int )
  2  partition by range (a)
  3  (partition p1 values less than (100),
  4  partition p2 values less than (500),
  5  partition p3 values less than (1200));
Table created.

接下来看一下howey的对象,在以下字典里看到.每个分区也是一个对象:
SQL> select object_name,object_type,object_id,status,data_object_id from user_objects;
OBJECT_NAME          OBJECT_TYPE          OBJECT_ID STATUS  DATA_OBJECT_ID
-------------------- ------------------- ---------- ------- --------------
TEST                 TABLE                   894492 VALID           894492
PK_A_TEST            INDEX                   894493 VALID           894493
TRI_TEST             TRIGGER                 894494 VALID
INT_TEST             TABLE PARTITION         894499 VALID           894499
INT_TEST             TABLE PARTITION         894498 VALID           894498
INT_TEST             TABLE PARTITION         894497 VALID           894497
INT_TEST             TABLE                   894496 VALID
7 rows selected.

3. 执行在线重定义.
SQL> exec dbms_redefinition.start_redef_table('howey','test','int_test','a a,b b,c c,0 d');
PL/SQL procedure successfully completed.

再次查看对象发现为9个.
SQL> select object_name,object_type,object_id,status,data_object_id from user_objects;
OBJECT_NAME          OBJECT_TYPE          OBJECT_ID STATUS  DATA_OBJECT_ID
-------------------- ------------------- ---------- ------- --------------
TEST                 TABLE                   894492 VALID           894492
PK_A_TEST            INDEX                   894493 VALID           894493
TRI_TEST             TRIGGER                 894494 VALID
INT_TEST             TABLE PARTITION         894499 VALID           894499
INT_TEST             TABLE PARTITION         894498 VALID           894498
INT_TEST             TABLE PARTITION         894497 VALID           894497
INT_TEST             TABLE                   894496 VALID
MLOG$_TEST           TABLE                   894500 VALID           894500
RUPD$_TEST           TABLE                   894501 VALID
9 rows selected.
多了2个表,一个永久表mlog$_emp ,此表记录test的快照日志.记录finish以前的变化记录.
另外一个是临时表,rupd$_emp 在mv刷新用到.此表在此不做讨论.
检查所有表的数据,以便和之后对照
SQL> select * from test;
         A          B C
---------- ---------- --------------------
         1        999 test table
         2        998 test table
         3        997 test table
         4        996 test table
.
.
.
       997          3 test table
       998          2 test table
       999          1 test table
      1000          0 test table
1000 rows selected.
发现中间表int_test会比test表多了一个字段.这就是我们在建立中间表的时候添加进去的.
SQL> select * from int_test where rownum < 10;

         A          B C                             D
---------- ---------- -------------------- ----------
         1        999 test table                    0
         2        998 test table                    0
.
.
.
       998          2 test table                    0
       999          1 test table                    0
      1000          0 test table                    0
1000 rows selected.
:SQL> select * from mlog$_test;
no rows selected

4. 复制原来表上面的constraint ,index, trigger,
查看test表相关的对象:
SQL>  select * from user_tab_privs_made;
GRANTEE    TABLE_NAME      GRANTO PRIVILEGE  GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM     TEST            HOWEY  INSERT     NO  NO
SYSTEM     TEST            HOWEY  SELECT     NO  NO

SQL> select trigger_name,table_owner ,table_name from user_triggers;
TRIGGER_NAME     TABLE_OWNER        TABLE_NAME
------------------------------ ------------------------------ ---------------
TRI_TEST              HOWEY                   TEST
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;

INDEX_NAME         INDEX_TYPE         TABLE_OWNER        TABLE_NAME
------------------------------ --------------------------- ------------------------------ ---------------
PK_A_TEST           NORMAL                  HOWEY              TEST

执行DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS 把原来表上面的trigger,index,constraint,复制到int_test表上面:
SQL> declare
  2  n number;
  3  begin
  4  dbms_redefinition.COPY_TABLE_DEPENDENTS('howey','test','int_test',1,true,true,true,true,n,true);
  5  end;
  6  /
PL/SQL procedure successfully completed.

SQL> select * from user_tab_privs_made;
GRANTEE    TABLE_NAME      GRANTO PRIVILEGE  GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM     TEST            HOWEY  INSERT     NO  NO
SYSTEM     INT_TEST        HOWEY  INSERT     NO  NO
SYSTEM     INT_TEST        HOWEY  SELECT     NO  NO
SYSTEM     TEST            HOWEY  SELECT     NO  NO

SQL>  select trigger_name,table_owner ,table_name from user_triggers;
TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ---------------
TRI_TEST                       HOWEY                          TEST
TMP$$_TRI_TEST0                HOWEY                          INT_TEST

SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME
------------------------------ --------------------------- ------------------------------ ---------------
TMP$$_PK_A_TEST0               NORMAL                      HOWEY                          INT_TEST
PK_A_TEST                      NORMAL                      HOWEY                          TEST

5.  执行dbms_redefinition.sync_interim_table , 刷新日志表中数据,使中间表数据和原来表中数据一致.
在test表中插入一条数据: 
SQL> insert into test values ( '1001','3','test data ');
1 row created.
再次查看mlog$_test表中的数据,多了一行数据:
SQL>  select * from mlog$_test;
         A SNAPTIME$ D O CHANGE_VECTOR$$
---------- --------- - - --------------------------------------------------
      1001 01-JAN-00 I N FE
SQL >  Select * from int_test; 还是1000行,test表插入一行之后,int_test表并没有发上变化

SQL> exec dbms_redefinition.sync_interim_table('howey','test','int_test');
PL/SQL procedure successfully completed.
同步之后我们在看以下表的数据发生了怎么样的变化:
Test ,int_test , mlog$_test  
看到此过程就是把日志里的数据转移到了中间表中, mlog$_test表数据为空.test表和int_test表内的数据完全一致
6. 最后工作.
再添加几个权限给中间表便于finish后的对比.(如果此时把权限赋予int_test表上面,则执行完finish过程后.权限会继承到test分区表上.)
grant update on test to system;
Grant succeeded.
SQL> select * from user_tab_privs_made;
GRANTEE    TABLE_NAME      GRANTO PRIVILEGE  GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM     TEST            HOWEY  INSERT     NO  NO
SYSTEM     INT_TEST        HOWEY  INSERT     NO  NO
SYSTEM     INT_TEST        HOWEY  SELECT     NO  NO
SYSTEM     TEST            HOWEY  UPDATE     NO  NO
SYSTEM     TEST            HOWEY  SELECT     NO  NO

7. 执行重构完成的过程(此过程的核心就是原表和中间表表名的互换)
SQL> exec dbms_redefinition.finish_redef_table('howey','test','int_test');
PL/SQL procedure successfully completed.
查看权限,index,trigger ,已经全部都转化过来了.
SQL> select * from user_tab_privs_made;
GRANTEE    TABLE_NAME      GRANTO PRIVILEGE  GRA HIE
---------- --------------- ------ ---------- --- ---
SYSTEM     INT_TEST        HOWEY  INSERT     NO  NO
SYSTEM     TEST            HOWEY  INSERT     NO  NO
SYSTEM     TEST            HOWEY  SELECT     NO  NO
SYSTEM     INT_TEST        HOWEY  UPDATE     NO  NO
SYSTEM     INT_TEST        HOWEY  SELECT     NO  NO

SQL>  select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME from user_indexes;
INDEX_NAME                     INDEX_TYPE                  TABLE_OWNER                    TABLE_NAME
------------------------------ --------------------------- ------------------------------ ---------------
PK_A_TEST                      NORMAL                      HOWEY                          TEST
TMP$$_PK_A_TEST0               NORMAL                      HOWEY                          INT_TEST

SQL> select trigger_name,table_owner ,table_name from user_triggers;
TRIGGER_NAME                   TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ---------------
TMP$$_TRI_TEST0                HOWEY                          INT_TEST
TRI_TEST                       HOWEY                          TEST

对象又到刚才的9个了,少了两个rupd$_test和mlog$_test. 已经被删除了.
SQL>  select object_name,object_type,object_id,status,data_object_id from user_objects;
OBJECT_NAME          OBJECT_TYPE          OBJECT_ID STATUS  DATA_OBJECT_ID
-------------------- ------------------- ---------- ------- --------------
TMP$$_TRI_TEST0      TRIGGER                 894494 INVALID
TRI_TEST             TRIGGER                 894503 INVALID
TMP$$_PK_A_TEST0     INDEX                   894493 VALID           894493
PK_A_TEST            INDEX                   894502 VALID           894502
INT_TEST             TABLE                   894492 VALID           894492
TEST                 TABLE PARTITION         894499 VALID           894499
TEST                 TABLE PARTITION         894498 VALID           894498
TEST                 TABLE PARTITION         894497 VALID           894497
TEST                 TABLE                   894496 VALID
从以下命名可以看到 ,转化已经成功完成.
SQL> desc test;
 Name                                                                Null?    Type
 ------------------------------------------------------------------- -------- ---------------------------------------------
 A                                                                            NUMBER(38)
 B                                                                            NUMBER(38)
 C                                                                            VARCHAR2(20)
 D                                                                            NUMBER(38)
SQL> desc int_test;
 Name                                                                Null?    Type
 ------------------------------------------------------------------- -------- ---------------------------------------------
 A                                                                   NOT NULL NUMBER(38)
 B                                                                            NUMBER(38)
 C                                                                            VARCHAR2(20)
oracle完成了普通表test到分区表int_test的转化工作. 
其实核心就是在数据字典中把两个表名互换了一下,这对系统的影响是相当小的.

8. 结束
删除中间表,index, trigger 
Drop table int_test ;

如若重定义失败 .或者想取消重定义过程,运行以下dbms包 ;
Dbms_redefinition.abort_redef_table(‘howey’,’test’,’int_test’) ;


这里就结束了 整个在线重构就完成了 .

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

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

注册时间:2008-09-12

  • 博文量
    22
  • 访问量
    48511