oracle在线重定义-普通表转分区表

前几天有一张40多G的普通表需要转为分区表，为了将对业务的影响降到最小，使用在线重定义的方式进行转换，使用这种方式转换，业务几乎感觉不到影响。

两种转换方式：若表有主键则基于主键，没有主键则基于rowid。

=================== 基于主键的在线重定义==============

一、准备阶段：
1、确认表能不能进行分区
1.1基于主键的确认

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('mcp2014','T_WGY_JFXF_LOG',DBMS_REDEFINITION.CONS_USE_PK);
END;
/

2、创建临时表：

CREATE TABLE T_WGY_JFXF_LOG_tmp
PARTITION BY RANGE (create_TIME)
   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') ) (
   PARTITION P201506
      VALUES LESS THAN
         (TO_DATE (' 2015-06-01 00:00:00',
                   'SYYYY-MM-DD HH24:MI:SS',
                   'NLS_CALENDAR=GREGORIAN')))
AS
   SELECT *
     FROM T_WGY_JFXF_LOG
    WHERE 1 <> 1;

3、检查字段类型、长度、索引、约束等是否一致。
非空约束不会创建过来，需要单独加：

ALTER TABLE T_WGY_JFXF_LOG_tmp MODIFY TYPE DEFAULT 0;
ALTER TABLE T_WGY_JFXF_LOG_tmp MODIFY result DEFAULT 0;

4、用户授权：

GRANT EXECUTE ON DBMS_REDEFINITION TO mcp2014;
GRANT ALL ON SYS.DBMS_REDEFINITION TO mcp2014;
GRANT EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE TO mcp2014;

二、开始重定义：
1、开始执行数据的迁移（原表向临时表迁移数据）：

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('MCP2014', 'T_WGY_JFXF_LOG', 'T_WGY_JFXF_LOG_TMP');

2、增量的数据迁移(该过程可选可不选)：
 

BEGIN 
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('mcp2014', 'T_WGY_JFXF_LOG', 'T_WGY_JFXF_LOG_TMP');
END;
/

3、进行权限对象的迁移

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('mcp2014', 'T_WGY_JFXF_LOG','T_WGY_JFXF_LOG_TMP',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

4、查询相关错误：

select object_name, base_table_name, ddl_txt from   DBA_REDEFINITION_ERRORS;

5、原表和临时表进行切换（这个过程也会进行增量数据的复制）：

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('mcp2014', 'T_WGY_JFXF_LOG', 'T_WGY_JFXF_LOG_TMP');
END;
/

三、回退：
如果再执行的过程中发生错误，可以通过以下语句结束整个过程，还原原表：

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'T_WGY_JFXF_LOG',
int_table => 'T_WGY_JFXF_LOG_TMP');
END; 

======================================================

 

 

====================== 基于rowid的在线重定义=============
一、准备阶段：
1、确认表能不能进行分区，基于rowid的确认：

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('mcp2014','T_WGY_JFXF_LOG',options_flag => dbms_redefinition.cons_use_rowid);
END;
/

2、创建临时表：

CREATE TABLE T_WGY_JFXF_LOG_tmp
PARTITION BY RANGE (create_TIME)
   INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') ) (
   PARTITION P201506
      VALUES LESS THAN
         (TO_DATE (' 2015-06-01 00:00:00',
                   'SYYYY-MM-DD HH24:MI:SS',
                   'NLS_CALENDAR=GREGORIAN')))
AS
   SELECT *
     FROM T_WGY_JFXF_LOG
    WHERE 1 <> 1;

3、检查字段类型、长度、索引、约束等是否一致。
非空约束不会创建过来，需要单独加：

alter table T_WGY_JFXF_LOG_tmp modify type default 0;
alter table T_WGY_JFXF_LOG_tmp modify result default 0;

4、用户授权：

GRANT EXECUTE ON DBMS_REDEFINITION TO mcp2014;
GRANT ALL ON SYS.DBMS_REDEFINITION TO mcp2014;
GRANT EXECUTE_CATALOG_ROLE,CREATE ANY TABLE,ALTER ANY TABLE,DROP ANY TABLE,LOCK ANY TABLE,SELECT ANY TABLE TO mcp2014;

二、开始重定义：
1、开始执行数据的迁移（原表向临时表迁移数据）：

EXEC DBMS_REDEFINITION.START_REDEF_TABLE('mcp2014', 'T_WGY_JFXF_LOG', 'T_WGY_JFXF_LOG_TMP',null,2);

2、增量的数据迁移(该过程可选可不选)：

BEGIN 
 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('mcp2014', 'T_WGY_JFXF_LOG', 'T_WGY_JFXF_LOG_TMP');
END;
/

3、进行权限对象的迁移

DECLARE
num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('mcp2014', 'T_WGY_JFXF_LOG','T_WGY_JFXF_LOG_TMP',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

4、查询相关错误：

select object_name, base_table_name, ddl_txt from   DBA_REDEFINITION_ERRORS;

5、原表和临时表进行切换（这个过程也会进行增量数据的复制）：

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('mcp2014', 'T_WGY_JFXF_LOG', 'T_WGY_JFXF_LOG_TMP');
END;
/

三、回退：
如果再执行的过程中发生错误，可以通过以下语句结束整个过程，还原原表：

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE(uname => 'SCOTT',
orig_table => 'T_WGY_JFXF_LOG',
int_table => 'T_WGY_JFXF_LOG_TMP');
END; 

===============================================