改造操作步驟：

1、檢查表能否進行線上重定義，透過主鍵或rowid兩種方法；

2、建立目標表結構;索引、主鍵等不用建立；

3、開始進行線上重定義，先全量同步一次資料；

4、同步索引和依賴的物件（包括索引、約束、觸發器、許可權等）；

5、增量同步資料；

6、完成線上重定義；

7、統計資訊收集，檢查表結構、索引狀態及並行度等

8、清理新增表，釋放空間；

-－－Created by Tony.Tang[TangYun]2016.03

SQL> desc DBMS_REDEFINITION

PROCEDURE ABORT_REDEF_TABLE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

PART_NAME VARCHAR2 IN DEFAULT

PROCEDURE CAN_REDEF_TABLE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

TNAME VARCHAR2 IN

OPTIONS_FLAG BINARY_INTEGER IN DEFAULT

PART_NAME VARCHAR2 IN DEFAULT

PROCEDURE COPY_TABLE_DEPENDENTS

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

COPY_INDEXES BINARY_INTEGER IN DEFAULT

COPY_TRIGGERS BOOLEAN IN DEFAULT

COPY_CONSTRAINTS BOOLEAN IN DEFAULT

COPY_PRIVILEGES BOOLEAN IN DEFAULT

IGNORE_ERRORS BOOLEAN IN DEFAULT

NUM_ERRORS BINARY_INTEGER OUT

COPY_STATISTICS BOOLEAN IN DEFAULT

COPY_MVLOG BOOLEAN IN DEFAULT

PROCEDURE FINISH_REDEF_TABLE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

PART_NAME VARCHAR2 IN DEFAULT

PROCEDURE REGISTER_DEPENDENT_OBJECT

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

DEP_TYPE BINARY_INTEGER IN

DEP_OWNER VARCHAR2 IN

DEP_ORIG_NAME VARCHAR2 IN

DEP_INT_NAME VARCHAR2 IN

PROCEDURE START_REDEF_TABLE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

COL_MAPPING VARCHAR2 IN DEFAULT

OPTIONS_FLAG BINARY_INTEGER IN DEFAULT

ORDERBY_COLS VARCHAR2 IN DEFAULT

PART_NAME VARCHAR2 IN DEFAULT

PROCEDURE SYNC_INTERIM_TABLE

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

PART_NAME VARCHAR2 IN DEFAULT

PROCEDURE UNREGISTER_DEPENDENT_OBJECT

Argument Name Type In/Out Default?

------------------------------ ----------------------- ------ --------

UNAME VARCHAR2 IN

ORIG_TABLE VARCHAR2 IN

INT_TABLE VARCHAR2 IN

DEP_TYPE BINARY_INTEGER IN

DEP_OWNER VARCHAR2 IN

DEP_ORIG_NAME VARCHAR2 IN

DEP_INT_NAME VARCHAR2 IN

DBMS_REDEFINITION包：

1、ABSORT_REDEF_TABLE：清理重定義的錯誤和中止重定義；

2、CAN_REDEF_TABLE：檢查表是否可以進行重定義,儲存過程執行成功代表可以進行重定義；

3、COPY_TABLE_DEPENDENTS：同步索引和依賴的物件（包括索引、約束、觸發器、許可權等）；

4、FINISH_REDEF_TABLE：完成線上重定義；

5、REGISTER_DEPENDENTS_OBJECTS：註冊依賴的物件，如索引、約束、觸發器等；

6、START_REDEF_TABLE：開始線上重定義；

7、SYNC_INTERIM_TABLE：增量同步資料；

8、UNREGISTER_DEPENDENT_OBJECT：不註冊依賴的物件，如索引、約束、觸發器等；

-－－Created by Tony.Tang[TangYun]2016.03

線上重定義預設採用基於主鍵方式進行；從Oracle 10g版本開始，可以支援基於ROWID，但不能用於索引組織表，重定義完成後會存在隱藏列M_ROW$$。

---源表

4.5 V4_RAMS PMAIN_TICKET_REFUND_TARGET

set long 49000

set longc 9999

set line 150

set pagesize 10000

SELECT dbms_metadata.get_ddl(upper('TABLE'),upper('PMAIN_TICKET_REFUND_TARGET'),upper('V4_RAMS')) from dual;

1、檢查源表是否可以進行線上重定義

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('V4_RAMS','PMAIN_TICKET_REFUND_TARGET',DBMS_REDEFINITION.CONS_USE_PK);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('V4_RAMS','PMAIN_TICKET_REFUND_TARGET',DBMS_REDEFINITION.CONS_USE_ROWID);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL>

-－－Created by Tony.Tang[TangYun]2016.03

2、建立目標表

---注意，這個在建立表時候最好不要使用主鍵，否則後面的索引都無法線上copy了，當然，也需要考慮索引是不是需要改造成本地索引。

CREATE TABLE "V4_RAMS"."PMAIN_TICKET_REFUND_SRC"

( "TNO" VARCHAR2(15) NOT NULL ENABLE,

"ISS_CO" CHAR(3) NOT NULL ENABLE,

"BSP_SI" VARCHAR2(2) NOT NULL ENABLE,

"ID_CODE" VARCHAR2(8) NOT NULL ENABLE,

"TIC_TYPE" VARCHAR2(10) NOT NULL ENABLE,

"T_FORM" CHAR(1) NOT NULL ENABLE,

"STAT_CODE" CHAR(1) NOT NULL ENABLE,

"ISS_FLAG" CHAR(1),

"ISS_DATE" DATE,

"AGENTCODE" VARCHAR2(8),

"AGENT_BATCH" VARCHAR2(12),

"CURRENCY_HD" CHAR(3),

"A_FARE" NUMBER(11,2),

"A_TAX" NUMBER(11,2),

"A_FEE" NUMBER(11,2),

"A_INSURE" NUMBER(11,2),

"A_COMM_RATE" NUMBER(5,4),

"A_COMM" NUMBER(11,2),

"A_PROMOTION_RATE" NUMBER(5,4),

"A_PROMOTION" NUMBER(11,2),

"D_FARE" NUMBER(11,2),

"D_TAX" NUMBER(11,2),

"D_FEE" NUMBER(11,2),

"D_INSURE" NUMBER(11,2),

"D_COMM_RATE" NUMBER(5,4),

"D_COMM" NUMBER(11,2),

"D_PROMOTION_RATE" NUMBER(5,4),

"D_PROMOTION" NUMBER(11,2),

"TKT_STR" VARCHAR2(4000),

"A_REFUND_FEE" NUMBER(11,2),

"D_REFUND_FEE" NUMBER(11,2),

"BANK_CODE" VARCHAR2(20),

"SALE_NO" VARCHAR2(30),

"AGENT_ID" VARCHAR2(50),

"ISS_OFFICE" VARCHAR2(10),

"S_BATCH_NO" VARCHAR2(15),

"MATCH_SI" CHAR(1),

"IMPORT_FLAG" CHAR(1),

"CREATE_DATE" DATE,

"CREATE_USER" VARCHAR2(20),

"PAYMENT" VARCHAR2(50),

"REMARK" VARCHAR2(100),

"FILE_SOURCE" VARCHAR2(60),

"T_ROUTE" VARCHAR2(90),

"PRD_TYPE" VARCHAR2(60),

"P_NAME" VARCHAR2(60),

"CURRENCY_PD" CHAR(3),

"FARE_PD" NUMBER(11,2),

"TAX_PD" NUMBER(11,2),

"FEE_PD" NUMBER(11,2),

"INSURE_PD" NUMBER(11,2),

"TOTAL_PD" NUMBER(11,2),

"FACE_STR" VARCHAR2(200),

"T_PARTS" NUMBER(3,0),

"COMPUTE_FLAG" VARCHAR2(2),

"CODE_TYPE1" VARCHAR2(10),

"GENERAL_CODE1" VARCHAR2(30),

"CODE_TYPE2" VARCHAR2(10),

"GENERAL_CODE2" VARCHAR2(30),

"SUB_OFFICE_NO" VARCHAR2(10),

"ENRS" VARCHAR2(150),

"RPSI" VARCHAR2(6),

"TRNC" CHAR(4),

"BSP_CENTER" VARCHAR2(10),

"S_AGENTCODE" VARCHAR2(8),

"B_WEIGHT" NUMBER(11,2),

"B_PIECE" NUMBER(5,0),

"BW_RATE" NUMBER(11,2),

"BP_RATE" NUMBER(11,2),

"B_RATE" NUMBER(11,2),

"WEIGHT_RANGE" VARCHAR2(20),

"OVER_PRICE" NUMBER(11,2),

"C_TYPE" VARCHAR2(10),

"UPDATED_TIME" DATE) partition by range(ISS_DATE)(

partition p201312 values less than (to_date('2014-01-01','yyyy-mm-dd')) tablespace USERS,

partition p201401 values less than (to_date('2014-02-01','yyyy-mm-dd')) tablespace USERS,

partition p201402 values less than (to_date('2014-03-01','yyyy-mm-dd')) tablespace USERS,

partition p201403 values less than (to_date('2014-04-01','yyyy-mm-dd')) tablespace USERS,

partition p201404 values less than (to_date('2014-05-01','yyyy-mm-dd')) tablespace USERS,

partition p201405 values less than (to_date('2014-06-01','yyyy-mm-dd')) tablespace USERS,

partition p201406 values less than (to_date('2014-07-01','yyyy-mm-dd')) tablespace USERS,

partition p201407 values less than (to_date('2014-08-01','yyyy-mm-dd')) tablespace USERS,

partition p201408 values less than (to_date('2014-09-01','yyyy-mm-dd')) tablespace USERS,

partition p201409 values less than (to_date('2014-10-01','yyyy-mm-dd')) tablespace USERS,

partition p201410 values less than (to_date('2014-11-01','yyyy-mm-dd')) tablespace USERS,

partition p201411 values less than (to_date('2014-12-01','yyyy-mm-dd')) tablespace USERS,

partition p201412 values less than (to_date('2015-01-01','yyyy-mm-dd')) tablespace USERS,

partition p201501 values less than (to_date('2015-02-01','yyyy-mm-dd')) tablespace USERS,

partition p201502 values less than (to_date('2015-03-01','yyyy-mm-dd')) tablespace USERS,

partition p201503 values less than (to_date('2015-04-01','yyyy-mm-dd')) tablespace USERS,

partition p201504 values less than (to_date('2015-05-01','yyyy-mm-dd')) tablespace USERS,

partition p201505 values less than (to_date('2015-06-01','yyyy-mm-dd')) tablespace USERS,

partition p201506 values less than (to_date('2015-07-01','yyyy-mm-dd')) tablespace USERS,

partition p201507 values less than (to_date('2015-08-01','yyyy-mm-dd')) tablespace USERS,

partition p201508 values less than (to_date('2015-09-01','yyyy-mm-dd')) tablespace USERS,

partition p201509 values less than (to_date('2015-10-01','yyyy-mm-dd')) tablespace USERS,

partition p201510 values less than (to_date('2015-11-01','yyyy-mm-dd')) tablespace USERS,

partition p201511 values less than (to_date('2015-12-01','yyyy-mm-dd')) tablespace USERS,

partition p201512 values less than (to_date('2016-01-01','yyyy-mm-dd')) tablespace USERS,

partition p201601 values less than (to_date('2016-02-01','yyyy-mm-dd')) tablespace USERS,

partition p201602 values less than (to_date('2016-03-01','yyyy-mm-dd')) tablespace USERS,

partition p201603 values less than (to_date('2016-04-01','yyyy-mm-dd')) tablespace USERS,

partition p201604 values less than (to_date('2016-05-01','yyyy-mm-dd')) tablespace USERS,

partition p201605 values less than (to_date('2016-06-01','yyyy-mm-dd')) tablespace USERS,

partition p201606 values less than (to_date('2016-07-01','yyyy-mm-dd')) tablespace USERS,

partition p201607 values less than (to_date('2016-08-01','yyyy-mm-dd')) tablespace USERS,

partition p201608 values less than (to_date('2016-09-01','yyyy-mm-dd')) tablespace USERS,

partition p201609 values less than (to_date('2016-10-01','yyyy-mm-dd')) tablespace USERS,

partition p201610 values less than (to_date('2016-11-01','yyyy-mm-dd')) tablespace USERS,

partition p201611 values less than (to_date('2016-12-01','yyyy-mm-dd')) tablespace USERS,

partition p201612 values less than (to_date('2017-01-01','yyyy-mm-dd')) tablespace USERS,

partition pmax values less than (maxvalue) tablespace USERS

);

3、開始線上重定義

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE('V4_RAMS','PMAIN_TICKET_REFUND_TARGET','PMAIN_TICKET_REFUND_SRC');

PL/SQL procedure successfully completed.

Elapsed: 00:01:34.34

SQL> select count(*) from V4_RAMS.PMAIN_TICKET_REFUND_TARGET;

COUNT(*)

----------

15937891

Elapsed: 00:00:01.31

SQL> select count(*) from V4_RAMS.PMAIN_TICKET_REFUND_SRC;

COUNT(*)

----------

15936587

Elapsed: 00:00:01.91

4、同步依賴物件

SQL> variable err_num number;

SQL> exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'V4_RAMS',orig_table=>'PMAIN_TICKET_REFUND_TARGET',int_table=>'PMAIN_TICKET_REFUND_SRC',copy_indexes=>dbms_redefinition.cons_orig_params,num_errors=>:err_num);

BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname=>'V4_RAMS',orig_table=>'PMAIN_TICKET_REFUND_TARGET',int_table=>'PMAIN_TICKET_REFUND_SRC',copy_indexes=>dbms_redefinition.cons_orig_params,num_errors=>:err_num); END;

*

ERROR at line 1:

ORA-01442: column to be modified to NOT NULL is already NOT NULL

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1119

ORA-06512: at "SYS.DBMS_REDEFINITION", line 2039

ORA-06512: at line 1

由於之前指定了主鍵，所以已經是not null 了，檢查可以看到索引已經建立成功。

Elapsed: 00:00:42.00

INDEX_NAME INDEX_COL INDEX_TYPE PAR

-------------------------------- ------------------------------ ---------------------- ---

V4_RAMS.TMP$$_PK_PMAIN_TICKET_RE TNO,ISS_CO,BSP_SI,ID_CODE,TIC_ NORMAL-UNIQUE NO

FUN0 TYPE

V4_RAMS.TMP$$_PMAIN_TICKET_TARGE ISS_DATE NORMAL-NONUNIQUE NO

T_I0

5、做一次增量資料同步，同步這段時間的增量資料

SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('V4_RAMS','PMAIN_TICKET_REFUND_TARGET','PMAIN_TICKET_REFUND_SRC');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09

SQL> select count(*) from V4_RAMS.PMAIN_TICKET_REFUND_TARGET;

COUNT(*)

----------

15936587

Elapsed: 00:00:01.32

SQL> select count(*) from V4_RAMS.PMAIN_TICKET_REFUND_SRC;

COUNT(*)

----------

15936587

Elapsed: 00:00:02.17

6、完成線上重定義

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('V4_RAMS','PMAIN_TICKET_REFUND_TARGET','PMAIN_TICKET_REFUND_SRC');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.91

結束重定義，此時會鎖表，交換表涉及的資料字典中的相關資料。

此時PMAIN_TICKET_REFUND_TARGET已經變更了分割槽表，PMAIN_TICKET_REFUND_SRC已經變更普通表，與原來PMAIN_TICKET_REFUND_TARGET的表結構一致。

7、收集表的統計資訊，檢查索引名、並行度等，檢查無效物件

INDEX_NAME INDEX_COL INDEX_TYPE PAR

-------------------------------- ------------------------------ ---------------------- ---

V4_RAMS.PK_PMAIN_TICKET_REFUND_TARGET TNO,ISS_CO,BSP_SI,ID_CODE,TIC_TYPE NORMAL-UNIQUE NO

V4_RAMS.PMAIN_TICKET_TARGET_ISS_DATE ISS_DATE NORMAL-NONUNIQUE NO

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'V4_RAMS',tabname=>'PMAIN_TICKET_REFUND_TARGET',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE,degree=>8);

PL/SQL procedure successfully completed.

Elapsed: 00:01:32.59

SQL> select degree,index_name,status from dba_indexes where table_name='PMAIN_TICKET_REFUND_TARGET' and owner='V4_RAMS';

DEGREE INDEX_NAME STATUS

---------------------------------------- ------------------------------ --------

1 PK_PMAIN_TICKET_REFUND_TARGET VALID

1 PMAIN_TICKET_TARGET_ISS_DATE VALID

8、清理新增表，釋放空間。

SQL> drop table V4_RAMS.PMAIN_TICKET_REFUND_SRC purge;

Table dropped.

Elapsed: 00:00:00.82

-－－Created by Tony.Tang[TangYun]2016.03

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

uname IN VARCHAR2,

orig_table IN VARCHAR2,

int_table IN VARCHAR2,

copy_indexes IN PLS_INTEGER := 1,

copy_triggers IN BOOLEAN := TRUE,

copy_constraints IN BOOLEAN := TRUE,

copy_privileges IN BOOLEAN := TRUE,

ignore_errors IN BOOLEAN := FALSE,

num_errors OUT PLS_INTEGER,

copy_statistics IN BOOLEAN := FALSE);

Parameter Description

uname The schema name of the tables.

orig_table The name of the table being redefined.

int_table The name of the interim table.

copy_indexes

A flag indicating whether to copy the indexes

0 - don't copy any index

dbms_redefinition.cons_orig_params - copy the indexes using the physical parameters of the source indexes

copy_triggers TRUE implies clone triggers, FALSE implies do nothing

copy_constraints TRUE implies clone constraints, FALSE implies do nothing. If compatibility setting is 10.2 or higher, then clone CHECK and NOT NULL constraints.

copy_privileges TRUE implies clone privileges, FALSE implies do nothing

ignore_errors TRUE implies if an error occurs while cloning a particular dependent object, then skip that object and continue cloning other dependent objects. FALSE implies that the cloning process should stop upon encountering an error.

num_errors The number of errors that occurred while cloning dependent objects

copy_statistics TRUE = copy statistics, FALSE = do nothing

Usage Notes

The user must check the column num_errors before proceeding to ensure that no errors occurred during the cloning of the objects.

In case of an error, the user should fix the cause of the error and call the COPY_TABLE_DEPENDENTS Procedure again to clone the dependent object. Alternatively the user can manually clone the dependent object and then register the manually cloned dependent object using the REGISTER_DEPENDENT_OBJECT Procedure.

All cloned referential constraints involving the interim tables will be created disabled (they will be automatically enabled after the redefinition) and all triggers on interim tables will not fire till the redefinition is completed. After the redefinition is complete, the cloned objects will be renamed to the corresponding pre-redefinition names of the objects (from which they were cloned from).

It is the user's responsibility that the cloned dependent objects are unaffected by the redefinition. All the triggers will be cloned and it is the user's responsibility that the cloned triggers are unaffected by the redefinition.

Created by Tony.Tang[TangYun]2016.03

－－－－－－－－－－－－－End－－－－－－－－－－－－－－－－－

使用線上重定義方法改造普通表為分割槽表實戰

相關文章