对已有表进行分区 在线重定义表为分区表

改造操作步骤：

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.