Online redefinition -- modify the column datatype

Oracle数据库提供一个DBMS_REDEFINITION套件，该套件的执行权限包含在EXECUTE_CATALOG_ROLE角色。

步骤：

Step1.选择模式，分为两种：
a.使用”主键“(预设)，原始表与目的表必须拥有相同的”主键“字段。
b.作用ROWID，要注意：原始表不能是索引组织表，执行后会包含一个隐藏字段(M_ROWS$$)，在过程结束后要删除的。

Step2.确认表是否可以进行Online redefinition，执行exec dbms_redefinition.can_redef_table

Step3.建立临时表，如果准备删除原始表的某些字段，就不需要加入临时表，反之，如果准备加入新字段，要直接定义在临时表中。

Step4.执行dbms_redefinition.start_redef_table

Step5.执行dbms_redefinition.copy_table_dependents程序，oracle 10g会自动在临时表上建立必要的索引，约束条件或是触发器。
在此步骤中，可以从dba_redefinition_errors数据字典视图内检查copy_table_dependents程序的执行过程是否产生错误。

Step6.执行dbms_redefinition.register_dependent_objects程序，这个程序用来更改目的表上新建立的索引，约束条件或触发器名称。(10g前，此动作要在完成后手动执行)

Step7.执行dbms_redefinition.finish_redef_table程序，完成Online redefinition
此程序执行过程中，oracle 10g会以互斥模式(exclusive mode)锁定原始表，不过锁定的时间极短。

以下是实例：

Online redefinition to modify the column datatype

由于业务需求，需要更改一个表字段的数据类型，原数据类型为nvarchar2,需要改为varchar2

以下为实验过程：

SQL> create table temp_ling (id number not null primary key,name nvarchar2(100));
 
Table created
 
SQL> desc temp_ling
Name Type           Nullable Default Comments
---- -------------- -------- ------- --------
ID   NUMBER                                  
NAME NVARCHAR2(100) Y                        
 
SQL> insert into temp_ling values (1,'aa');
 
1 row inserted
 
SQL> insert into temp_ling values (2,'cc');
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from temp_ling;
 
        ID NAME
---------- --------------------------------------------------------------------------------
         1 aa
         2 cc
 
SQL> alter table temp_ling modify name varchar2(100);
 
alter table temp_ling modify name varchar2(100)
 
ORA-01439: column to be modified must be empty to change datatype
 
SQL> create table temp_ling_t (id number,name varchar2(100));
 
Table created
 
SQL> desc temp_ling
Name Type           Nullable Default Comments
---- -------------- -------- ------- --------
ID   NUMBER                                  
NAME NVARCHAR2(100) Y                        
 
SQL> desc temp_ling_t
Name Type          Nullable Default Comments
---- ------------- -------- ------- --------
ID   NUMBER        Y                        
NAME VARCHAR2(100) Y                        
 
SQL> exec dbms_redefinition.can_redef_table('PURCHASE', 'temp_ling', dbms_redefinition.cons_use_pk);
 
begin dbms_redefinition.can_redef_table('PURCHASE', 'temp_ling', dbms_redefinition.cons_use_pk); end;
 
ORA-12089: cannot online redefine table "PURCHASE"."TEMP_LING" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 137
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1479
ORA-06512: at line 2
 
SQL> alter table temp_ling add constraint pk_temp_ling primary key(ID);
 
Table altered
 
Executed in 0.032 seconds
 
SQL> exec dbms_redefinition.can_redef_table('PURCHASE', 'temp_ling', dbms_redefinition.cons_use_pk);
 
PL/SQL procedure successfully completed
 
SQL>
SQL> begin
  2    dbms_redefinition.start_redef_table('PURCHASE', 'temp_ling', 'temp_ling_t','id id, to_char(name) name',
  3     dbms_redefinition.cons_use_pk);
  4   end;
  5  /
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_redefinition.finish_redef_table('PURCHASE', 'temp_ling', 'temp_ling_t');
 
PL/SQL procedure successfully completed
 
SQL> desc temp_ling
Name Type          Nullable Default Comments
---- ------------- -------- ------- --------
ID   NUMBER        Y                        
NAME VARCHAR2(100) Y                        
 
SQL> desc temp_ling_t
Name Type           Nullable Default Comments
---- -------------- -------- ------- --------
ID   NUMBER                                  
NAME NVARCHAR2(100) Y                        

至此，实验成功结束。需要注意的地方：如果已经执行了dbms_redefinition.start_redef_table存储过程，没有成功的话，再次再执行要先执行一次dbms_redefinition.abort_redef_table(如exec dbms_redefinition.ABORT_REDEF_TABLE('PURCHASE', 'COUNTY', 'temp_ling_county');)，用来中断”在线重定义“动作，并清除过程中产生的错误讯息。

另外，此次实验得知了另一小知识点：原来utf8编码中，一个汉字是占三个字节，oh my god,,,,,原来一直以为是两个。:)

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

转载于:http://blog.itpub.net/24387280/viewspace-681787/