将一个非分区表转换为分区表

/*
功能：
    将一个非分区表转换为分区表
基本思路：
    SST_DAY_TOTAL是(数据量上百万条，列比较多)一个非分区表，此时创建一个与SST_DAY_TOTAL同结构的分区表zhaozhenlong_partition，维护数据，
删除SST_DAY_TOTAL表，将zhaozhenlong_partition更名为SST_DAY_TOTA，删除zhaozhenlong_partition
*/
--具体步骤：
--1、查询非分区表数据
select to_char(rpt_date, 'yyyymm'), count(*) from SST_DAY_TOTAL group by to_char(rpt_date, 'yyyymm') order by to_char(RPT_DATE, 'yyyymm');

--2、创建分区表（结构和非分区表SST_DAY_TOTAL相同）
create table zhaozhenlong_partition
(
  GAS_ID                 VARCHAR2(12) not null,
  RPT_DATE               DATE not null,
  INV_NO                 VARCHAR2(12),
  ......
)
tablespace sdlg 
partition by range(rpt_date)(
partition rest values less than (maxvalue))

--3、交换数据（数据从非分区表到分区表）
alter table zhaozhenlong_partition exchange partition rest with table SST_DAY_TOTAL;

--4、查询分区表数据
select to_char(RPT_DATE, 'mm-yyyy'), count(*) from SST_DAY_TOTAL group by to_char(RPT_DATE, 'mm-yyyy');

--辅助脚本
/*
          select 'alter table zhaozhenlong_partition split partition rest at (to_date(''' 
                 || to_char(RPT_DATE, 'yyyymm') ||''',''yyyymm'')) into (partition p' 
                 || to_char(RPT_DATE, 'yyyymm')
                 || ', partition rest);'
          from zhaozhenlong_partition 
          group by to_char(RPT_DATE, 'yyyymm') 
          order by to_char(RPT_DATE, 'yyyymm')
*/

--5、对rest分区进行拆分
alter table zhaozhenlong_partition split partition rest at (to_date('200608','yyyymm')) into (partition p200608, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200609','yyyymm')) into (partition p200609, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200610','yyyymm')) into (partition p200610, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200611','yyyymm')) into (partition p200611, partition rest);
alter table zhaozhenlong_partition split partition rest at (to_date('200612','yyyymm')) into (partition p200612, partition rest);

--6、删除非分区表
drop table SST_DAY_TOTAL

--7、将分区表重命名为原非分区表名
alter table zhaozhenlong_partition rename to SST_DAY_TOTAL 

--8、删除中间处理过程的分区表
drop table zhaozhenlong_partition

 

________________________________

--补充
如果已存在分区表，要将某未分区表（与已分区表结构完全相同）加入到分区中：

alter table SST_DAY_TOTAL exchange partition p200608 with table zhaozhenlong_1;
alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2;

如果zhaozhenlong_2不符合分区规则，则会报错，则需要指定without validation 来禁止检查
alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2 without validation;

当然，如果你确认zhaozhenlong_1表中的数据是符合分区规则的，那指定without validation 之后，不会对zhaozhenlong_1进行全表扫描，
则会缩短exchange时间


REDEFINITION在线重定义表


7*24的业务运营系统中，对一个表进行在线重定义将是很棘手的事，因为业务实时的在对这个表进行进行插入，删除修改等操作，那么是否可以在线重定义表 呢,Oracle9i提供了在线重定义表包（DBMS_REDEFINITION），在修改表结构的同时可以进行DML操作。

1.构造原始数据表
SQL> create table red_test   --原始表，需要对这个表进行在线重定义
  2  as
  3  select  te_seq.nextval as id ,t.owner,t.object_name,10 as tvalue from dba_objects t ;
Table created

SQL> alter table RED_TEST --增加主键
  2    add constraint pri_redtest primary key (id);
Table altered

SQL> create index idc_retest_owner on red_test(owner) ;
Index created

需求：把red_test进行在线重定义，把tvuale字段更改为sal字段，并且sal=tvalue*10

2.验证是否可以在线重定义
SQL> EXEC Dbms_Redefinition.Can_Redef_Table('etl', 'red_test',DBMS_REDEFINITION.CONS_USE_PK);
PL/SQL procedure successfully completed

3.建立中间表
SQL> CREATE TABLE RED_TEST_MIDDLE AS  
  2  SELECT id,owner,object_name,tvalue as sal FROM red_test WHERE 1=2;
Table created

SQL> alter table RED_TEST_MIDDLE
  2    add constraint pri_redtest123 primary key (id);
Table altered

4.进行在线重定义
SQL> EXEC Dbms_Redefinition.Start_Redef_Table( 'ETL','RED_TEST','RED_TEST_MIDDLE','ID
ID, OWNER OWNER, OBJECT_NAME OBJECT_NAME,TVALUE*10 SAL');

PL/SQL procedure successfully completed

6.同步中间表，主要是同步重定义过程中变化的数据
SQL> EXEC dbms_redefinition.sync_interim_table('ETL', 'RED_TEST', 'RED_TEST_MIDDLE');

PL/SQL procedure successfully completed

7.测试在原始表继续插入是否还会同步
SQL> insert into RED_TEST select  te_seq.nextval as id ,t.owner,t.object_name,999 from dba_objects t ;
34193 rows inserted

SQL> commit;
Commit complete
--重定义过程原始表发生dml的数据会保留MV log，这就是为什么原始表要有主键的原因，当然也可以利用rowid
SQL> select count(*) from Mlog$_red_test;--原始产生的dml操作会记录在mv log

  COUNT(*)
----------
     34193

8.完成重定义
SQL> EXEC Dbms_Redefinition.Finish_Redef_Table( 'ETL', 'RED_TEST', 'RED_TEST_MIDDLE');
PL/SQL procedure successfully completed

9.检查是否完成了重定义
SQL> select ix.table_name,ix.index_name from user_indexes ix where ix.table_name='RED_TEST';

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
RED_TEST                       PRI_REDTEST123

SQL> select ix.table_name,ix.index_name from user_indexes ix where ix.table_name='RED_TEST_MIDDLE' ;

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
RED_TEST_MIDDLE                IDC_RETEST_OWNER
RED_TEST_MIDDLE                PRI_REDTEST

--比较一下中间表和原始表的索引，发觉仅仅是同步了数据，索引并不能同步过去，所以在正式环境中，应该先建立索引在完成重定义，建立索引可以放在第5步来做

10.删除中间表
SQL> drop table RED_TEST_MIDDLE ;

Table dropped
------------------------------------------------------------------------------------

一般会有这个需求，需要将在线大表（5G以上）更改为分区表，Dbms_Redefinition在线重定义非常慢，可能需要好几个小时，大表又是实时的 业务操作，这就存在很大的失败风险。有朋友曾经在线重定义过程中失败导致业务瘫痪，建议还是少用，或是不用，知道有这么回事就可以了。

个人觉得如下步骤比较保险，但是需要停机
a) export the table
b) create a new empty table that has the partition definition on it
c) import the table with IGNORE=Y

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

转载于:http://blog.itpub.net/660456/viewspace-429539/