从Oracle迁移到GreenPlum（一）分区表的迁移

1. 获取分区表的DDL语句

对于Oracle数据库，使用dbms_metadata.get_ddl包来获取Oracle的分区表的建表语句，但是默认情况下，会存在Oracle特有的与其他数据库不兼容的语法，所以我们需要剔除掉这些语法

--关闭存储、表空间属性
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);
--关闭创建表的PCTFREE、NOCOMPRESS等属性
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
--关闭表索引、外键等关联
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',false);

执行SQL，就可以获得建表语句·

select dbms_metadata.get_ddl('TABLE','BDRPRGST_H','AEDEV') from dual;

CREATE TABLE "AEDEV"."BDRPRGST_H" 
   (	"USERSEQNO" CHAR(12), 
	"PBUID" VARCHAR2(5), 
	"REGSTS" CHAR(1), 
	"DATELSTUPDDAT" NUMBER(18,0), 
	"INVACCTTYPCOD" CHAR(1), 
	"INVACCTNO" CHAR(10), 
	"TRANDAT" NUMBER, 
	"TRANTIM" NUMBER, 
	"BATCH_NUM" NUMBER(20,0) DEFAULT 0
   ) 
  PARTITION BY RANGE ("TRANDAT") 
 ( 
 PARTITION "BDRPRGST_H_20210523"  VALUES LESS THAN (20210524) , 
 PARTITION "BDRPRGST_H_20210524"  VALUES LESS THAN (20210525) , 
 PARTITION "BDRPRGST_H_20210525"  VALUES LESS THAN (20210526) , 
 PARTITION "BDRPRGST_H_20210526"  VALUES LESS THAN (20210527) , 
 PARTITION "BDRPRGST_H_20210527"  VALUES LESS THAN (20210528) , 
 PARTITION "BDRPRGST_H_20210528"  VALUES LESS THAN (20210529) , 
 PARTITION "BDRPRGST_H_20210529"  VALUES LESS THAN (20210530) , 
 PARTITION "BDRPRGST_H_20210530"  VALUES LESS THAN (20210531) , 
 PARTITION "BDRPRGST_H_20210531"  VALUES LESS THAN (20210601) )

2.分析

我们需要注意的是Oracle的很多数据类型，与其他的MySQL数据库，或者PostgreSQL是不兼容的，所以，我们将分区表的建表语句，拆分成两个部分

PART1

CREATE TABLE "AEDEV"."BDRPRGST_H" 
  (	"USERSEQNO" CHAR(12), 
   "PBUID" VARCHAR2(5), 
   "REGSTS" CHAR(1), 
   "DATELSTUPDDAT" NUMBER(18,0), 
   "INVACCTTYPCOD" CHAR(1), 
   "INVACCTNO" CHAR(10), 
   "TRANDAT" NUMBER, 
   "TRANTIM" NUMBER, 
   "BATCH_NUM" NUMBER(20,0) DEFAULT 0
  ) 

PART2

  PARTITION BY RANGE ("TRANDAT") 
( 
PARTITION "BDRPRGST_H_20210102"  VALUES LESS THAN (20210101) , 
PARTITION "BDRPRGST_H_20210524"  VALUES LESS THAN (20210525) , 
PARTITION "BDRPRGST_H_20210525"  VALUES LESS THAN (20210526) , 
PARTITION "BDRPRGST_H_20210526"  VALUES LESS THAN (20210527) , 
PARTITION "BDRPRGST_H_20210527"  VALUES LESS THAN (20210528) , 
PARTITION "BDRPRGST_H_20210528"  VALUES LESS THAN (20210529) , 
PARTITION "BDRPRGST_H_20210529"  VALUES LESS THAN (20210530) , 
PARTITION "BDRPRGST_H_20210530"  VALUES LESS THAN (20210531) , 
PARTITION "BDRPRGST_H_20210531"  VALUES LESS THAN (20210601) )

所以对于PART1字段定义部分，跟普通数据表是完全一致的，所以这儿我们直接舍弃，后面可以通过其他方式构建表的定义部分，这儿先卖个关子，这个我在后面再进行总结，所以对Oracle数据库分区表的信息提取，最终要的就是PART2部分。

由于目前在进行的是从Oracle到GreenPlum的迁移工作，所以以GP来进行介绍

GP来说，主要使用三种分区方式

1. 范围分区（range）
   分区语法:

create table test_partition_range
(
	id int, 
	name varchar(64), 
	fdate varchar(64)
	) distributed by (id) 
	partition by range(fdate) 
	(
		partition p1 start ('2021-01-01') inclusive end ('2017-01-31') exclusive, 
		partition p2 start ('2022-12-01') inclusive end ('2017-02-29') exclusive, 
		default partition default_p
	);
	
	inclusive ：指定包含，例如上面的 start ('2017-01-01') inclusive 则是包含'2017-01-01'
	exclusive ： 指定不包含， 例如上面的 end ('2017-01-31') exclusive 则是不包含'2017-01-31'
	对于inclusive，exclusive来说，是GP范围分区的默认值，所以这块，我们也可以忽略

2. 快速分区（every）
   分区语法:

create table test_partition_every_1 
(
	id int, 
	name varchar(64), 
	fdate date
) 
distributed by (id) 
partition by range (fdate) 
(
	partition pn_ start ('2017-01-01'::date) end ('2017-12-31'::date) every ('1 day'::interval), 
	default partition default_p
);
every：指定跨越基数,支持日期，数值等各种类型

一般来说，通过构建GP的快速分区，可以帮助我们快速的创建分区，但是它本身存在缺陷，创建的分区是定长的，从我们生产导出的实际的分区表的DDL语句可以看出

 PARTITION "BDRPRGST_H_20210101"  VALUES LESS THAN (20210102) , 
 PARTITION "BDRPRGST_H_20210524"  VALUES LESS THAN (20210525) , 
 PARTITION "BDRPRGST_H_20210525"  VALUES LESS THAN (20210526) , 

分区存在一些不确定的跨度，所以以GP的快速分区方式来构建分区表的DDL语句，暂时被放弃

3. list分区（list）
   分区语法:

create table test_partition_list 
(
	id int, 
	name varchar(64), 
	fdate varchar(10)
) 
distributed by (id) 
partition by list (fdate) 
(
	partition p1 values ('2017-01-01', '2017-01-02'), 
	partition p2 values ('2017-01-03'), 
	default partition pd
);

LIST分区，没啥好说的，该咋样还是咋样

3.信息提取

所以，最终经过评估还是按照完全一一对应的方式，进行信息提取，我们修改了SQL提取的语法，最终得到了我们想要的有效信息

select substr(replace(replace(dbms_metadata.get_ddl('TABLE','BDRPRGST_H','AEDEV'),'"'),'VALUES LESS THAN','END'),
instr(replace(replace(dbms_metadata.get_ddl('TABLE','BDRPRGST_H','AEDEV'),'"'),'VALUES LESS THAN','END'),'PARTITION'))
from dual;

就得到我们想要的GP的分区表的构建语法

PARTITION BY RANGE (TRANDAT) 
 ( 
 PARTITION BDRPRGST_H_20210102  END (20210101) , 
 PARTITION BDRPRGST_H_20210524  END (20210525) , 
 PARTITION BDRPRGST_H_20210525  END (20210526) , 
 PARTITION BDRPRGST_H_20210526  END (20210527) , 
 PARTITION BDRPRGST_H_20210527  END (20210528) , 
 PARTITION BDRPRGST_H_20210528  END (20210529) , 
 PARTITION BDRPRGST_H_20210529  END (20210530) , 
 PARTITION BDRPRGST_H_20210530  END (20210531) , 
 PARTITION BDRPRGST_H_20210531  END (20210601) )

4.总结

经过上述分析，我们找到了提取分区表有效信息的方法，在原有SQL的基础上进行改进，就可以提取Oracle数据库全量的用户的分区表创建的关键性信息

select rownum,owner，tabname，substr(replace(replace(dbms_metadata.get_ddl('TABLE',tabname,owner),'"'),'VALUES LESS THAN','END'),
instr(replace(replace(dbms_metadata.get_ddl('TABLE',tabname,owner),'"'),'VALUES LESS THAN','END'),'PARTITION'))
from 
(
select distinct table_owner as owner, table_name as tabname from dba_tab_partitions 
where table_owner in
(
select name from sys.user$ 
            where type#=1 and spare4 is not null 
            and datats# >3 and password is not null 
            and to_char(ctime,'yyyy-mm-dd') <>(select to_char(min(ctime),'yyyy-mm-dd') from sys.user$)
            and name not in ('HR','OE','IX','SH','PM','BI')      
)
);