Oracle 11G DBMS_REDEFINITION修改表数据类型

SYS@honor1 > SET LONG 999999
SYS@honor1 > SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_REDE','HR') FROM DUAL;
CREATE TABLE "HR"."TEST_REDE"
  (    "OWNER" VARCHAR2(30),
       "OBJECT_NAME" VARCHAR2(128),
       "SUBOBJECT_NAME" VARCHAR2(30),
       "OBJECT_ID" VARCHAR2(20),
       "DATA_OBJECT_ID" NUMBER,
       "OBJECT_TYPE" VARCHAR2(19),
       "CREATED" DATE,
       "LAST_DDL_TIME" DATE,
       "TIMESTAMP" VARCHAR2(19),
       "STATUS" VARCHAR2(7),
       "TEMPORARY" VARCHAR2(1),
       "GENERATED" VARCHAR2(1),
       "SECONDARY" VARCHAR2(1),
       "NAMESPACE" NUMBER,
       "EDITION_NAME" VARCHAR2(30)
  ) 
 TABLESPACE "USERS";

# 由于表没有主键，所以只能使用ROWID，如果表有主键，可以删掉CONS_USE_ROWID，默认使用PK
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('HR','TEST_REDE',
DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

CREATE TABLE "HR"."TEST_REDE_INTER"
(    "OWNER" VARCHAR2(30),
     "OBJECT_NAME" VARCHAR2(128),
     "SUBOBJECT_NAME" VARCHAR2(30),
     "OBJECT_ID" VARCHAR2(20),
     "DATA_OBJECT_ID" VARCHAR2(10),         --需要修改后的数据类型
     "OBJECT_TYPE" VARCHAR2(19),
     "CREATED" DATE,
     "LAST_DDL_TIME" DATE,
     "TIMESTAMP" VARCHAR2(19),
     "STATUS" VARCHAR2(7),
     "TEMPORARY" VARCHAR2(1),
     "GENERATED" VARCHAR2(1),
     "SECONDARY" VARCHAR2(1),
     "NAMESPACE" NUMBER,
     "EDITION_NAME" VARCHAR2(30)
)
TABLESPACE "USERS";

alter session force parallel dml parallel 4;
alter session force parallel query parallel 4;
BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE(
uname => 'HR',
orig_table => 'TEST_REDE',
int_table => 'TEST_REDE_INTER',
col_mapping => 
'OWNER OWNER,       
OBJECT_NAME OBJECT_NAME,
SUBOBJECT_NAME SUBOBJECT_NAME,
OBJECT_ID OBJECT_ID,
to_char(DATA_OBJECT_ID) DATA_OBJECT_ID,    --由于更改数据类型，需要手工转换
OBJECT_TYPE OBJECT_TYPE,
CREATED CREATED,
LAST_DDL_TIME LAST_DDL_TIME,
TIMESTAMP TIMESTAMP,
STATUS STATUS,
TEMPORARY TEMPORARY,
GENERATED GENERATED,
SECONDARY SECONDARY,
NAMESPACE NAMESPACE,
EDITION_NAME EDITION_NAME',
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

begin
    dbms_redefinition.sync_interim_table(uname        => '&USERNAME',
                                           orig_table => '&SOURCE_TAB',
                                           int_table  => '&INT_TAB');
end;
/

# 如果更改了列名，或者增加列，必要时，手工创建相关索引等对象
DECLARE
    num_errors PLS_INTEGER;
BEGIN
    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => '&USERNAME',
                                            orig_table       => '&SOURCE_TAB',
                                            int_table        => '&INT_TAB',
                                            copy_indexes     => DBMS_REDEFINITION.cons_orig_params,
                                            copy_triggers    => TRUE,
                                            copy_constraints => TRUE,
                                            copy_privileges  => TRUE,
                                            ignore_errors    => FALSE,
                                            num_errors       => num_errors,
                                            copy_statistics  => TRUE);
END;
/