【笨汤】Tony.Tang汤云。。。

平生皆被读书误!

  • 博客访问: 1672553
  • 博文数量: 109
  • 用 户 组: 普通用户
  • 注册时间: 1970-01-01 08:00
  • 认证徽章:
个人简介

有空写写一写,没空看一看。。。 微信号:tangyun0925

文章存档

2018年(6)

2017年(5)

2016年(12)

2015年(17)

2014年(21)

2013年(19)

2012年(10)

2011年(4)

2010年(15)

分类: Oracle

2016-03-25 14:54:13

改造操作步骤:

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


阅读(9518) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册