oracle 在线重定义

一、前言
    现在的业务应用，要求停机时间也非常短，有的甚至对停机零容忍。在这种情况下，有些业务在设计初期考虑不周全，到了一定时候必须要进行调整，比如说普通表到分区表的转换问题。
    对于上亿条记录的普通表，转换成分区表，按传统的方式，肯定会有较长的停机时间。从oracle9i开始，提供了一个在线重定义的方式，只需要非常短的停机时间。

二、我们就在线重定义进行说明的举例。
1、在线重定义，会占用两倍的表空间，所以要事先准备好
2、在线重定义的包
DBMS_REDEFINITION
对于这个包，里面有很多项目，如下：
DBMS_REDEFINITION.START_REDEF_TABLE  这里面主要有三个参数传入：用户、原表、建的中间表
DBMS_REDEFINITION.START_REDEF_TABLE  这里面涉及到表列的修改的，主要是列名方面的修改，不能修改data type
dbms_redefinition.sync_interim_table 数据同步，在同步的过程中，可能产生较多的新数据，需要同步一下
DBMS_REDEFINITION.FINISH_REDEF_TABLE 对表进行最后的定义修改
3、在线重定义是通过物化视图来做的，如果在过程中失败了，要重新开始，需要进行清理

三、案例说明

1、统计该表信息
--数据量

SQL> select count(*) from mugua.mytest;
 
  COUNT(*)
----------
  45220113
 
--表定义
CREATE TABLE "MUGUA"."MYTEST"
   ( "MTREPORTSEQ_NO" NUMBER NOT NULL ENABLE,
 "GATEWAY_ID" VARCHAR2(20),
 "GMSG_ID" VARCHAR2(32) NOT NULL ENABLE,
 "SEND_STAT" VARCHAR2(50) NOT NULL ENABLE,
 "RECV_MOBILE" VARCHAR2(30),
 "RECV_TIME" DATE DEFAULT SYSDATE,
 "ERROR_CODE" VARCHAR2(20),
  CONSTRAINT "PK_MYTEST" PRIMARY KEY ("MTREPORTSEQ_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "test_IDX"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "test_DATA"

可以看到主键列：PK_MYTEST，为普通表

--索引情况
SQL> SELECT index_name,table_name,column_name FROM dba_ind_columns where table_name = 'MYTEST';
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------------------------------
PK_MYTEST                      MYTEST                          MTREPORTSEQ_NO

索引所在的空间：test_indx

--计划分区字段：recv_time

--主外键：
主外键：
SQL> select a.owner 主键拥有者,a.table_name 主键表,b.column_name 主键列,C.OWNER 外键拥有者,c.table_name 外键表,d.column_name 外键列
  2  from user_constraints  a
  3  left join user_cons_columns b on  a.constraint_name=b.constraint_name left join user_constraints C  ON C.R_CONSTRAINT_NAME=a.constraint_name
  4  left join user_cons_columns d on c.constraint_name=d.constraint_name
  5  where  a.constraint_type='P' and  a.table_name='MYTEST' --需要查看主外键关系的表
  6  order by a.table_name

这个主键不存在对外键的约束
2、数据表在线重定义
2.1根据原表定义建立中间表，创建相应的分区表，以recv_time为分区字段
SQL>CREATE TABLE "MUGUA"."MYTEST_TMP"
   ( "MTREPORTSEQ_NO" NUMBER NOT NULL ENABLE,
 "GATEWAY_ID" VARCHAR2(20),
 "GMSG_ID" VARCHAR2(32) NOT NULL ENABLE,
 "SEND_STAT" VARCHAR2(50) NOT NULL ENABLE,
 "RECV_MOBILE" VARCHAR2(30),
 "RECV_TIME" DATE DEFAULT SYSDATE,
 "ERROR_CODE" VARCHAR2(20))
 partition by range(recv_time)
(partition y2011_q4 values less than (to_date('2012-01-01','yyyy-mm-dd')),
 partition y2012_q1 values less than (to_date('2012-04-01','yyyy-mm-dd')),
 partition y2012_q2 values less than (to_date('2012-07-01','yyyy-mm-dd')),
 partition y2012_q3 values less than (to_date('2012-10-01','yyyy-mm-dd')),
 partition y2012_q4 values less than (to_date('2013-01-01','yyyy-mm-dd')),
 partition year_q values less than (maxvalue));

Table created.

2.2检查是否可以是否满足在线定义的条件

SQL> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('MUGUA','MYTEST',1);

PL/SQL procedure successfully completed.

2.3使用在线定义包开始转换原表数据及拷贝原表索引、trigger等等，注意同义词不能拷贝

SQL> exec DBMS_REDEFINITION.START_REDEF_TABLE (uname => 'MUGUA',orig_table => 'MYTEST',int_table => 'MYTEST_TMP');
 
PL/SQL procedure successfully completed.

SQL> DECLARE
  2  error_count pls_integer := 0;
  3  BEGIN
  4  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname =>'MUGUA',orig_table  => 'MYTEST',int_table => 'MYTEST_TMP',ignore_errors=> TRUE,num_errors 
=>error_count);
  5  DBMS_OUTPUT.PUT_LINE('errors := ' || TO_CHAR(error_count));
  6  END;
  7  /

PL/SQL procedure successfully completed.
2.4因为是在线做的，所以有必要同步一下数据
SQL> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE (uname => 'MUGUA',orig_table  => 'MYTEST',int_table  => 'MYTEST_TMP');
PL/SQL procedure successfully completed.

2.5收集中间表统计信息，结束在线重定义工作
SQL> EXEC DBMS_STATS.gather_table_stats('MUGUA', 'MYTEST_TMP', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname   => 'MUGUA',orig_table  => 'MYTEST',int_table  => 'MYTEST_TMP');

PL/SQL procedure successfully completed.
2.6删除中间表
SQL> DROP TABLE mugua.mytest_TMP PURGE;

Table dropped.
3主键索引不变，其它索引可以考虑成分区索引
本表除主键外没有其它索引
四、数据剥离(以MYTEST为例)
1确定要剥离的表和分区等
--找出需要剥离的表和分区，记录下表名及分区名，例如：要剥离这个分区：y2011_q4
SQL> select * from dba_tab_partitions where table_name = 'MYTEST' and owner = 'MUGUA';
--找出该表上的索引
SQL> SELECT index_name,table_name,column_name FROM dba_ind_columns where table_name = 'MYTEST';
 
INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ --------------------------------------------
PK_MYTEST                      MYTEST                         MTREPORTSEQ_NO2
2创建对应的接收表
SQL> create table mugua.mytest_BAK20111230 as select * from mugua.mytest where 0 = 1;
3验证数据
select count(*) from mugua.mytest partition(y2011_q4);
4数据剥离，并核对数据
SQL>alter table mugua.mytest exchange partition y2011_q4 with table  MUGUA.MYTEST_BAK20111230 without validation update global indexes;

SQL> select count(*) from MUGUA.MYTEST_BAK20111230;
5删除原分区
SQL> alter table mugua.mytest drop partition y2011_q4 ;

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29371470/viewspace-1062756/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29371470/viewspace-1062756/