Oracle 11G DBMS_REDEFINITION修改表資料型別

你好我是李白發表於2020-07-12
Oracle資料型別

11G DBMS_REDEFINITION修改表資料型別

1.獲取源表結構資訊 

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";

2.驗證是否可以線上重定義 

# 由於表沒有主鍵，所以只能使用ROWID，如果表有主鍵，可以刪掉CONS_USE_ROWID，預設使用PK
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('HR','TEST_REDE',
DBMS_REDEFINITION.CONS_USE_ROWID);
END;
/

3.建立中間表，修改需要修改的列資料型別 

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";

4.開始線上重定義  

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;
/

5.同步中間資料，減少finish_redef_table時間 

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

6.複製相關約束，依賴物件 

# 如果更改了列名，或者增加列，必要時，手工建立相關索引等物件
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;
/

7.完成線上重定義 

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

參考：

https://blog.csdn.net/bikeorcl/article/details/103974032


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31439444/viewspace-2703986/，如需轉載，請註明出處，否則將追究法律責任。

相關文章