使用在线重定义方法改造普通表为分区表实战
改造操作步骤：
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－－－－－－－－－－－－－－－－－
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
