项目中遇到的，对greenplum不是很熟悉，所以花了一天完成的东西，主要用来将
oracle的表定义信息转换成greenplum形式，可以实现批量化，不用powerdisger一
个个点了。

功能说明：

将oracle的表（表结构定义、约束、默认值及分区信息）定义信息转换成greenplum
格式。

包说明：

[backcolor=rgb(242, 242, 242)]点击(此处)折叠或打开
[font=Consolas, monospace][list=1]
[*]create or replace package metadata_ora2gp as

[*] function split[color=#00cc](in_tablist in varchar2[color=#00cc]) return dbms_sql[color=#00cc].varchar2a[color=#00cc];
[*] procedure convert_tab_create_ddl[color=#00cc](in_tablist varchar2[color=#00cc])[color=#00cc];
[*] procedure convert_index_create_ddl[color=#00cc](in_tablist varchar2[color=#00cc])[color=#00cc];
[*] procedure convert_constraint_ddl[color=#00cc](in_tablist varchar2[color=#00cc])[color=#00cc];
[*]end[color=#00cc];
[*][color=#00cc]/

包体：

[backcolor=rgb(242, 242, 242)]点击(此处)折叠或打开
[font=Consolas, monospace][list=1]
[*]create or replace package body metadata_ora2gp as

[*]
[*] function split[color=#00cc](in_tablist in varchar2[color=#00cc]) return dbms_sql[color=#00cc].varchar2a as
[*] v_tablist dbms_sql[color=#00cc].varchar2a[color=#00cc];
[*] begin
[*]
[*] select distinct trim[color=#00cc](regexp_substr[color=#00cc](regexp_replace[color=#00cc](in_tablist[color=#00cc],
[*] [color=#ff0ff]'([^ .

[*]]+) +|[.]([^ .
[*]]+)'[color=#00cc],
[*] [color=#ff0ff]'\1.\2'[color=#00cc])[color=#00cc],
[*] [color=#ff0ff]'[^

[*]]+'[color=#00cc],
[*] 1[color=#00cc],
[*] level[color=#00cc])[color=#00cc]) bulk collect
[*] into v_tablist
[*] from dual
[*] connect by level [color=#00cc]<[color=#00cc]= regexp_count[color=#00cc](in_tablist[color=#00cc], chr[color=#00cc](10[color=#00cc])[color=#00cc]) [color=#00cc]- 1[color=#00cc]; [*] [*] return v_tablist[color=#00cc]; [*] end split[color=#00cc]; [*] [*] procedure convert_tab_create_ddl[color=#00cc](in_tablist varchar2[color=#00cc]) as [*] [color=#ff990]/* [*] Author: Zhangyu@BI [*] CreateDate: 2014/05/14 [*] Version: 1.0Beta [*] Function: convert oracle table create ddl to greenplum table create ddl. [*] [*] 11g支持分区模式：RANGE-RANGE、LIST-RANGE、LIST-HASH和LIST-LIST。 [*] */ [*] v_dk varchar2[color=#00cc](4000[color=#00cc])[color=#00cc]; [color=#ff990]--greenplum dk键 [*] v_ddl_stmt clob[color=#00cc]; [color=#ff990]--ddl语句变量 [*] v_tablist dbms_sql[color=#00cc].varchar2a[color=#00cc]; [color=#ff990]--表名数组，格式为owner.table_name [*] [*] [color=#ff990]--列信息游标，包括字段类型、默认值及非空值等 [*] cursor cur_collist[color=#00cc](in_tab varchar2[color=#00cc]) is [*] select mtc[color=#00cc].OWNER[color=#00cc], [*] mtc[color=#00cc].TABLE_NAME[color=#00cc], [*] mtc[color=#00cc].COLUMN_NAME[color=#00cc], [*] mtc[color=#00cc].DATA_TYPE[color=#00cc], [*] mtc[color=#00cc].DATA_LENGTH[color=#00cc], [*] mtc[color=#00cc].DATA_PRECISION[color=#00cc], [*] mtc[color=#00cc].DATA_SCALE[color=#00cc], [*] mtc[color=#00cc].NULLABLE[color=#00cc], [*] mtc[color=#00cc].COLUMN_ID[color=#00cc], [*] replace[color=#00cc](lower[color=#00cc](mtc[color=#00cc].DATA_DEFAULT[color=#00cc])[color=#00cc], [color=#ff0ff]'sysdate'[color=#00cc], [color=#ff0ff]'current_date'[color=#00cc]) as DATA_DEFAULT[color=#00cc], [color=#ff990]--greenplum中没有sysdate函数 [*] t[color=#00cc].gp_type_name [*] from sys[color=#00cc].my_tab_columns mtc[color=#00cc], ORA2GP_TYPE_MAPPING t [*] where mtc[color=#00cc].OWNER [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| mtc[color=#00cc].TABLE_NAME in [color=#00cc](upper[color=#00cc](in_tab[color=#00cc])[color=#00cc]) [*] and mtc[color=#00cc].DATA_TYPE [color=#00cc]= t[color=#00cc].ora_type_name[color=#00cc]([color=#00cc]+[color=#00cc]) [*] order by mtc[color=#00cc].COLUMN_ID[color=#00cc]; [*] [*] type collist_tab is table of cur_collist[color=#00cc]%rowtype[color=#00cc]; [*] v_collist collist_tab[color=#00cc]; [*] [*] v_finalcol varchar2[color=#00cc](32767[color=#00cc])[color=#00cc]; [color=#ff990]--存放单列信息的变量 [*] [*] type part_rec is record[color=#00cc]( [*] col1 varchar2[color=#00cc](4000[color=#00cc])[color=#00cc], [*] col2 varchar2[color=#00cc](4000[color=#00cc])[color=#00cc])[color=#00cc]; [*] type part_tab is table of part_rec[color=#00cc]; [*] v_part part_tab[color=#00cc]; [color=#ff990]--存放单个分区信息的数组 [*] [*] v_partsql clob[color=#00cc]; [color=#ff990]--存放最终分区语句的变量 [*] [*] begin [*] [color=#ff990]/************************************************************************************/ [*] [color=#ff990]--拆分参数存放入表清单数组 [*] [color=#ff990]/* [*] 格式必须为： [*] owner.table_name [*] 或者 [*] owner table_name [*] */ [*] v_tablist [color=#00cc]:[color=#00cc]= metadata_ora2gp[color=#00cc].split[color=#00cc](in_tablist[color=#00cc])[color=#00cc]; [*] [*] [color=#ff990]/************************************************************************************/ [*] [color=#ff990]--生成表定义语句 [*] [*] for loop_idx in 1 [color=#00cc].[color=#00cc]. v_tablist[color=#00cc].count loop [*] [*] open cur_collist[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc]; [*] [*] if cur_collist[color=#00cc]%notfound then [*] v_collist [color=#00cc]:[color=#00cc]= null[color=#00cc]; [*] dbms_output[color=#00cc].put_line[color=#00cc]([color=#ff0ff]'Error: no ' [color=#00cc]|[color=#00cc]| v_tablist[color=#00cc](loop_idx[color=#00cc]) [color=#00cc]|[color=#00cc]| [*] [color=#ff0ff]' table.'[color=#00cc])[color=#00cc]; [*] [*] else [*] [*] fetch cur_collist bulk collect [*] into v_collist[color=#00cc]; [*] close cur_collist[color=#00cc]; [*] [*] end if[color=#00cc]; [*] [*] for inner_idx in 1 [color=#00cc].[color=#00cc]. v_collist[color=#00cc].count loop [*] [*] [color=#ff990]--列映射转换 [*] select v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].column_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]' ' [color=#00cc]|[color=#00cc]| case [*] when v_collist[color=#00cc](inner_idx[color=#00cc]) [*] [color=#00cc].data_type in [color=#00cc]([color=#ff0ff]'VARCHAR2'[color=#00cc], [color=#ff0ff]'NVARCHAR2'[color=#00cc], [color=#ff0ff]'CHAR'[color=#00cc], [color=#ff0ff]'NCHAR'[color=#00cc]) then [*] v_collist[color=#00cc](inner_idx[color=#00cc]) [*] [color=#00cc].gp_type_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].data_length [color=#00cc]|[color=#00cc]| [color=#ff0ff]')' [*] when v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].data_type like [color=#ff0ff]'TIMESTAMP%' then [*] v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].data_type [*] else [*] v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].gp_type_name [*] end [color=#00cc]|[color=#00cc]| case [*] when v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].nullable [color=#00cc]= [color=#ff0ff]'N' then [*] [color=#ff0ff]' NOT NULL ' [*] else [*] [color=#ff0ff]' ' [*] end [color=#00cc]|[color=#00cc]| case [*] when v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].data_default is not null then [*] [color=#ff0ff]' DEFAULT ' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].data_default [*] else [*] [color=#ff0ff]' ' [*] end [*] into v_finalcol [*] from dual[color=#00cc]; [*] [*] [color=#ff990]--拼接create table sql [*] if inner_idx [color=#00cc]= 1 then [*] if v_collist[color=#00cc].count [color=#00cc]= 1 then [*] v_ddl_stmt [color=#00cc]:[color=#00cc]= [color=#ff0ff]'create table ' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc]) [*] [color=#00cc].table_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]| v_finalcol [color=#00cc]|[color=#00cc]| [color=#ff0ff]')' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc])[color=#00cc]; [*] else [*] v_ddl_stmt [color=#00cc]:[color=#00cc]= [color=#ff0ff]'create table ' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc]) [*] [color=#00cc].table_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]| v_finalcol [color=#00cc]|[color=#00cc]| [color=#ff0ff]',' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc])[color=#00cc]; [*] end if[color=#00cc]; [*] elsif inner_idx [color=#00cc]= v_collist[color=#00cc].last then [*] [*] [color=#ff990]/************************************************************************************/ [*] [color=#ff990]--生成distrubited key [*] [color=#00cc]<[color=#00cc][color=#00cc]>
[*] begin
[*] with t as
[*] [color=#00cc](select listagg[color=#00cc](dic[color=#00cc].COLUMN_NAME[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by dic[color=#00cc].COLUMN_POSITION[color=#00cc]) as collist[color=#00cc],
[*] count[color=#00cc]([color=#00cc]*[color=#00cc]) as col_cnt[color=#00cc],
[*] min[color=#00cc](count[color=#00cc]([color=#00cc]*[color=#00cc])[color=#00cc]) over[color=#00cc]([color=#00cc]) min_col_cnt
[*] from dba_indexes dc[color=#00cc], dba_ind_columns dic
[*] where dc[color=#00cc].table_owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name in
[*] [color=#00cc](upper[color=#00cc](v_collist[color=#00cc](inner_idx[color=#00cc])
[*] [color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| v_collist[color=#00cc](inner_idx[color=#00cc])[color=#00cc].table_name[color=#00cc])[color=#00cc])
[*] and dc[color=#00cc].uniqueness [color=#00cc]= [color=#ff0ff]'UNIQUE'
[*] and dc[color=#00cc].owner [color=#00cc]= dic[color=#00cc].INDEX_OWNER
[*] and dc[color=#00cc].index_name [color=#00cc]= dic[color=#00cc].INDEX_NAME
[*] group by dic[color=#00cc].INDEX_OWNER[color=#00cc], dic[color=#00cc].INDEX_NAME[color=#00cc])
[*] select [color=#ff0ff]' DISTRIBUTED BY(' [color=#00cc]|[color=#00cc]| collist [color=#00cc]|[color=#00cc]| [color=#ff0ff]') '
[*] into v_dk
[*] from t
[*] where col_cnt [color=#00cc]= min_col_cnt[color=#00cc];
[*]
[*] exception
[*] when others then
[*] v_dk [color=#00cc]:[color=#00cc]= [color=#ff0ff]''[color=#00cc];
[*] end[color=#00cc];
[*] [color=#00cc]<[color=#00cc][color=#00cc]>
[*]
[*] v_ddl_stmt [color=#00cc]:[color=#00cc]= v_ddl_stmt [color=#00cc]|[color=#00cc]| v_finalcol [color=#00cc]|[color=#00cc]| [color=#ff0ff]')' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc]) [color=#00cc]|[color=#00cc]| v_dk[color=#00cc];
[*] else
[*] v_ddl_stmt [color=#00cc]:[color=#00cc]= v_ddl_stmt [color=#00cc]|[color=#00cc]| v_finalcol [color=#00cc]|[color=#00cc]| [color=#ff0ff]',' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc])[color=#00cc];
[*] end if[color=#00cc];
[*]
[*] end loop[color=#00cc];
[*] [color=#ff990]/************************************************************************************/
[*] [color=#ff990]--生成分区语句
[*] [color=#00cc]<[color=#00cc][color=#00cc]>
[*] begin
[*]
[*] with t1 as
[*] [color=#00cc](select dpt[color=#00cc].partitioning_type[color=#00cc], dpt[color=#00cc].subpartitioning_type
[*] from dba_part_tables dpt
[*] where dpt[color=#00cc].partitioning_type not in [color=#00cc]([color=#ff0ff]'HASH'[color=#00cc])
[*] and dpt[color=#00cc].subpartitioning_type not in [color=#00cc]([color=#ff0ff]'HASH'[color=#00cc])
[*] [color=#ff990]--gp默认是hash分区
[*] [color=#ff990]--缺陷：list-hash分区也被排除
[*] and dpt[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dpt[color=#00cc].table_name in
[*] [color=#00cc](upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc])[color=#00cc])[color=#00cc],
[*] t2 as
[*] [color=#00cc](select listagg[color=#00cc](dpkc[color=#00cc].column_name[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by dpkc[color=#00cc].column_position[color=#00cc]) pkcol
[*] from dba_part_key_columns dpkc
[*] where dpkc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dpkc[color=#00cc].name [color=#00cc]= upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc])[color=#00cc],
[*] t3 as
[*] [color=#00cc](select listagg[color=#00cc](dskc[color=#00cc].column_name[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by dskc[color=#00cc].column_position[color=#00cc]) spkcol
[*] from dba_subpart_key_columns dskc
[*] where dskc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dskc[color=#00cc].name [color=#00cc]= upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc])[color=#00cc],
[*] t4 as
[*] [color=#00cc](select [color=#ff0ff]' partition by ' [color=#00cc]|[color=#00cc]| partitioning_type [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]| pkcol [color=#00cc]|[color=#00cc]| [color=#ff0ff]')' [color=#00cc]|[color=#00cc]|
[*] chr[color=#00cc](10[color=#00cc]) [color=#00cc]|[color=#00cc]| [color=#ff0ff]' subpartition by ' [color=#00cc]|[color=#00cc]| subpartitioning_type [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]|
[*] spkcol [color=#00cc]|[color=#00cc]| [color=#ff0ff]')' part_col[color=#00cc],
[*] t1[color=#00cc].[color=#00cc]*[color=#00cc],
[*] t2[color=#00cc].[color=#00cc]*[color=#00cc],
[*] t3[color=#00cc].[color=#00cc]*
[*] from t1
[*] left outer join t2
[*] on 1 [color=#00cc]= 1
[*] left outer join t3
[*] on 1 [color=#00cc]= 1[color=#00cc])[color=#00cc],
[*] t5 as
[*] [color=#00cc](select dtp[color=#00cc].partition_name[color=#00cc],
[*] dtp[color=#00cc].partition_position[color=#00cc],
[*] case
[*] when regexp_like[color=#00cc](dtp[color=#00cc].high_value[color=#00cc], [color=#ff0ff]'^TO_DATE *[(]'[color=#00cc]) then
[*] [color=#ff0ff]'timestamp '[color=#ff0ff]'' [color=#00cc]|[color=#00cc]|
[*] trim[color=#00cc](regexp_substr[color=#00cc](dtp[color=#00cc].high_value[color=#00cc], [color=#ff0ff]'[^'[color=#ff0ff]']+'[color=#00cc], 1[color=#00cc], 2[color=#00cc])[color=#00cc]) [color=#00cc]|[color=#00cc]| [color=#ff0ff]''[color=#ff0ff]''
[*] else
[*] dtp[color=#00cc].high_value
[*] end as high_value[color=#00cc], [color=#ff990]--修
[*] dtsp[color=#00cc].partition_name as ppname[color=#00cc],
[*] dtsp[color=#00cc].subpartition_name[color=#00cc],
[*] dtsp[color=#00cc].subpartition_position[color=#00cc],
[*] case
[*] when regexp_like[color=#00cc](dtsp[color=#00cc].high_value[color=#00cc], [color=#ff0ff]'^TO_DATE *[(]'[color=#00cc]) then
[*] [color=#ff0ff]'timestamp '[color=#ff0ff]'' [color=#00cc]|[color=#00cc]|
[*] trim[color=#00cc](regexp_substr[color=#00cc](dtsp[color=#00cc].high_value[color=#00cc], [color=#ff0ff]'[^'[color=#ff0ff]']+'[color=#00cc], 1[color=#00cc], 2[color=#00cc])[color=#00cc]) [color=#00cc]|[color=#00cc]| [color=#ff0ff]''[color=#ff0ff]''
[*] else
[*] dtsp[color=#00cc].high_value
[*] end as sphval [color=#ff990]--修
[*] from sys[color=#00cc].my_tab_partitions dtp[color=#00cc], sys[color=#00cc].my_tab_subpartitions dtsp
[*] where dtp[color=#00cc].table_owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dtp[color=#00cc].table_name [color=#00cc]=
[*] upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])
[*] and dtp[color=#00cc].table_name [color=#00cc]= dtsp[color=#00cc].table_name[color=#00cc]([color=#00cc]+[color=#00cc])
[*] and dtp[color=#00cc].table_owner [color=#00cc]= dtsp[color=#00cc].table_owner[color=#00cc]([color=#00cc]+[color=#00cc])
[*] and dtp[color=#00cc].partition_name [color=#00cc]= dtsp[color=#00cc].partition_name[color=#00cc]([color=#00cc]+[color=#00cc])[color=#00cc])[color=#00cc],
[*] t6 as
[*] [color=#00cc](select case
[*] when upper[color=#00cc](high_value[color=#00cc]) in [color=#00cc]([color=#ff0ff]'DEFAULT'[color=#00cc], [color=#ff0ff]'MAXVALUE'[color=#00cc]) then
[*] [color=#ff0ff]' DEFAULT PARTITION other '
[*] else
[*] [color=#ff0ff]' partition ' [color=#00cc]|[color=#00cc]| partition_name [color=#00cc]|[color=#00cc]| case
[*] when partitioning_type [color=#00cc]= [color=#ff0ff]'RANGE' then
[*] [color=#ff0ff]' start (' [color=#00cc]|[color=#00cc]| high_value [color=#00cc]|[color=#00cc]| [color=#ff0ff]') INCLUSIVE '
[*] when partitioning_type [color=#00cc]= [color=#ff0ff]'LIST' then
[*] [color=#ff0ff]' values(' [color=#00cc]|[color=#00cc]| high_value [color=#00cc]|[color=#00cc]| [color=#ff0ff]')'
[*] else
[*] null
[*] end
[*] end as p_key[color=#00cc],
[*] case
[*] when upper[color=#00cc](sphval[color=#00cc]) in [color=#00cc]([color=#ff0ff]'DEFAULT'[color=#00cc], [color=#ff0ff]'MAXVALUE'[color=#00cc]) then
[*] [color=#ff0ff]' DEFAULT SUBPARTITION other '
[*] else
[*] [color=#ff0ff]' subpartition ' [color=#00cc]|[color=#00cc]| subpartition_name [color=#00cc]|[color=#00cc]| case
[*] when subpartitioning_type [color=#00cc]= [color=#ff0ff]'RANGE' then
[*] [color=#ff0ff]' start(' [color=#00cc]|[color=#00cc]| sphval [color=#00cc]|[color=#00cc]| [color=#ff0ff]') INCLUSIVE'
[*] when subpartitioning_type [color=#00cc]= [color=#ff0ff]'LIST' then
[*] [color=#ff0ff]' values(' [color=#00cc]|[color=#00cc]| sphval [color=#00cc]|[color=#00cc]| [color=#ff0ff]')'
[*] else
[*] null
[*] end
[*] end as sp_key[color=#00cc],
[*] t4[color=#00cc].[color=#00cc]*[color=#00cc],
[*] t5[color=#00cc].[color=#00cc]*
[*] from t4[color=#00cc], t5[color=#00cc])[color=#00cc],
[*] t7 as
[*] [color=#00cc](select lag[color=#00cc](p_key[color=#00cc]) over[color=#00cc](order by partition_position[color=#00cc], subpartition_position[color=#00cc]) as lg_pkey[color=#00cc],
[*] sp_key[color=#00cc],
[*] partition_position[color=#00cc],
[*] count[color=#00cc]([color=#00cc]*[color=#00cc]) over[color=#00cc]([color=#00cc]) as cnt[color=#00cc],
[*] row_number[color=#00cc]([color=#00cc]) over[color=#00cc](order by partition_position[color=#00cc], subpartition_position[color=#00cc]) row_cnt[color=#00cc],
[*] count[color=#00cc](subpartition_position[color=#00cc]) over[color=#00cc](partition by partition_position[color=#00cc]) spcnt[color=#00cc],
[*] subpartition_position[color=#00cc],
[*] p_key[color=#00cc],
[*] part_col
[*] from t6[color=#00cc])[color=#00cc],
[*] t8 as
[*] [color=#00cc](select case
[*] when p_key [color=#00cc]= lg_pkey then
[*] null
[*] else
[*] p_key
[*] end as p_key[color=#00cc],
[*] t7[color=#00cc].sp_key[color=#00cc],
[*] t7[color=#00cc].cnt[color=#00cc],
[*] t7[color=#00cc].row_cnt[color=#00cc],
[*] t7[color=#00cc].spcnt[color=#00cc],
[*] t7[color=#00cc].partition_position[color=#00cc],
[*] t7[color=#00cc].subpartition_position[color=#00cc],
[*] t7[color=#00cc].part_col
[*] from t7[color=#00cc])
[*] select regexp_replace[color=#00cc](regexp_replace[color=#00cc](case
[*] when p_key is not null then
[*] p_key [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc]) [color=#00cc]|[color=#00cc]| [color=#ff0ff]'('
[*] else
[*] null
[*] end [color=#00cc]|[color=#00cc]| case
[*] when row_cnt [color=#00cc]= cnt then
[*] sp_key [color=#00cc]|[color=#00cc]| [color=#ff0ff]')' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc])
[*] when spcnt [color=#00cc]= subpartition_position then
[*] sp_key [color=#00cc]|[color=#00cc]| [color=#ff0ff]'),' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc])
[*] else
[*] sp_key [color=#00cc]|[color=#00cc]| [color=#ff0ff]',' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc])
[*] end[color=#00cc],
[*] [color=#ff0ff]'[(] subpartition ,$'[color=#00cc],
[*] [color=#ff0ff]','[color=#00cc])[color=#00cc],
[*] [color=#ff0ff]'[(] subpartition [)]$'[color=#00cc]) key_list[color=#00cc],
[*] t8[color=#00cc].part_col bulk collect
[*] into v_part
[*] from t8
[*] order by partition_position[color=#00cc], subpartition_position[color=#00cc];
[*] exception
[*] when others then
[*] v_part [color=#00cc]:[color=#00cc]= null[color=#00cc];
[*] end[color=#00cc];
[*] [color=#00cc]<[color=#00cc][color=#00cc]>
[*]
[*] for loop_idx in 1 [color=#00cc].[color=#00cc]. v_part[color=#00cc].count loop
[*] v_partsql [color=#00cc]:[color=#00cc]= v_partsql [color=#00cc]|[color=#00cc]| v_part[color=#00cc](loop_idx[color=#00cc])[color=#00cc].col1[color=#00cc];
[*] end loop[color=#00cc];
[*]
[*] if v_part[color=#00cc].count [color=#00cc]<[color=#00cc]> 0 then
[*]
[*] v_partsql [color=#00cc]:[color=#00cc]= regexp_replace[color=#00cc](v_part[color=#00cc](1[color=#00cc])[color=#00cc].col2[color=#00cc],
[*] [color=#ff0ff]'subpartition by NONE[(][)]'[color=#00cc]) [color=#00cc]|[color=#00cc]|
[*] chr[color=#00cc](10[color=#00cc]) [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]| chr[color=#00cc](10[color=#00cc]) [color=#00cc]|[color=#00cc]| v_partsql [color=#00cc]|[color=#00cc]| [color=#ff0ff]')'[color=#00cc];
[*] else
[*] v_part [color=#00cc]:[color=#00cc]= null[color=#00cc];
[*] end if[color=#00cc];
[*]
[*] [color=#ff990]/************************************************************************************/
[*] [color=#ff990]--将分区语句拼接到sql中
[*] v_ddl_stmt [color=#00cc]:[color=#00cc]= v_ddl_stmt [color=#00cc]|[color=#00cc]| v_partsql[color=#00cc];
[*]
[*] dbms_output[color=#00cc].put_line[color=#00cc](v_ddl_stmt [color=#00cc]|[color=#00cc]| [color=#ff0ff]';'[color=#00cc])[color=#00cc];
[*] v_partsql [color=#00cc]:[color=#00cc]= [color=#ff0ff]''[color=#00cc];
[*] end loop[color=#00cc];
[*]
[*] end convert_tab_create_ddl[color=#00cc];
[*]
[*] procedure convert_index_create_ddl[color=#00cc](in_tablist in varchar2[color=#00cc]) as
[*] [color=#ff990]/*

[*] Author: Zhangyu@BI
[*] CreateDate: 2014/05/14
[*] Version: 1.0Beta
[*] Function: convert oracle index create ddl to greenplum index create ddl.
[*] */
[*] v_idxlist dbms_sql[color=#00cc].varchar2a[color=#00cc];
[*] v_tablist dbms_sql[color=#00cc].varchar2a[color=#00cc];
[*]
[*] begin
[*] [color=#ff990]/************************************************************************************/
[*] [color=#ff990]--拆分参数存放入表清单数组
[*] [color=#ff990]/*

[*] 格式必须为：
[*] owner.table_name
[*] 或者
[*] owner table_name
[*] */
[*] v_tablist [color=#00cc]:[color=#00cc]= metadata_ora2gp[color=#00cc].split[color=#00cc](in_tablist[color=#00cc])[color=#00cc];
[*]
[*] for loop_idx in 1 [color=#00cc].[color=#00cc]. v_tablist[color=#00cc].count loop
[*]
[*] with t1 as
[*] [color=#00cc](select di[color=#00cc].owner[color=#00cc],
[*] di[color=#00cc].index_name[color=#00cc],
[*] di[color=#00cc].uniqueness[color=#00cc],
[*] di[color=#00cc].index_type[color=#00cc],
[*] dic[color=#00cc].COLUMN_NAME[color=#00cc],
[*] partitioned[color=#00cc],
[*] di[color=#00cc].table_owner[color=#00cc],
[*] di[color=#00cc].table_name[color=#00cc],
[*] dic[color=#00cc].COLUMN_POSITION[color=#00cc],
[*] [color=#ff0ff]' ' DESCEND [color=#ff990]--gp中无升降概念
[*] from dba_indexes di[color=#00cc], dba_ind_columns dic
[*] where di[color=#00cc].owner [color=#00cc]= dic[color=#00cc].INDEX_OWNER
[*] and di[color=#00cc].index_name [color=#00cc]= dic[color=#00cc].INDEX_NAME
[*] and di[color=#00cc].table_owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| di[color=#00cc].table_name in
[*] [color=#00cc](upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc])
[*] and di[color=#00cc].index_type not like [color=#ff0ff]'FUNCTION-BASED%'[color=#00cc])[color=#00cc],
[*] t2 as
[*] [color=#00cc](select owner[color=#00cc],
[*] index_name[color=#00cc],
[*] uniqueness[color=#00cc],
[*] table_owner[color=#00cc],
[*] partitioned[color=#00cc],
[*] table_name[color=#00cc],
[*] index_type[color=#00cc],
[*] listagg[color=#00cc](COLUMN_NAME [color=#00cc]|[color=#00cc]| [color=#ff0ff]' ' [color=#00cc]|[color=#00cc]| DESCEND[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by COLUMN_POSITION[color=#00cc]) collist
[*] from t1
[*] group by owner[color=#00cc],
[*] index_name[color=#00cc],
[*] uniqueness[color=#00cc],
[*] index_type[color=#00cc],
[*] partitioned[color=#00cc],
[*] table_owner[color=#00cc],
[*] table_name[color=#00cc])
[*] select [color=#ff0ff]'create ' [color=#00cc]|[color=#00cc]| case
[*] when index_type [color=#00cc]= [color=#ff0ff]'BITMAP' then
[*]
[*] index_type
[*] when uniqueness [color=#00cc]= [color=#ff0ff]'UNIQUE' then
[*] uniqueness
[*] else
[*] null
[*] end [color=#00cc]|[color=#00cc]| [color=#ff0ff]' index ' [color=#00cc]|[color=#00cc]| t2[color=#00cc].index_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]' on ' [color=#00cc]|[color=#00cc]|
[*] table_owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| t2[color=#00cc].table_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]'(' [color=#00cc]|[color=#00cc]| collist [color=#00cc]|[color=#00cc]| [color=#ff0ff]') ' [color=#00cc]|[color=#00cc]|
[*] [color=#ff990]--dpi.locality
[*] [color=#ff0ff]';' as idx_crtsql bulk collect
[*] into v_idxlist
[*] from t2[color=#00cc], dba_part_indexes dpi
[*] where t2[color=#00cc].owner [color=#00cc]= dpi[color=#00cc].owner[color=#00cc]([color=#00cc]+[color=#00cc])
[*] and t2[color=#00cc].index_name [color=#00cc]= dpi[color=#00cc].index_name[color=#00cc]([color=#00cc]+[color=#00cc])[color=#00cc];
[*]
[*] for loop_idx in 1 [color=#00cc].[color=#00cc]. v_idxlist[color=#00cc].count loop
[*] dbms_output[color=#00cc].put_line[color=#00cc](v_idxlist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc];
[*] end loop[color=#00cc];
[*]
[*] end loop[color=#00cc];
[*]
[*] end convert_index_create_ddl[color=#00cc];
[*]
[*] procedure convert_constraint_ddl[color=#00cc](in_tablist varchar2[color=#00cc]) as
[*] [color=#ff990]/*

[*] Author: Zhangyu@BI
[*] CreateDate: 2014/05/14
[*] Version: 1.0Beta
[*] Function: convert oracle index create ddl to greenplum index create ddl.
[*] */
[*] v_conslist dbms_sql[color=#00cc].varchar2a[color=#00cc];
[*] v_tablist dbms_sql[color=#00cc].varchar2a[color=#00cc];
[*]
[*] begin
[*] [color=#ff990]/************************************************************************************/
[*] [color=#ff990]--拆分参数存放入表清单数组
[*] [color=#ff990]/*

[*] 格式必须为：
[*] owner.table_name
[*] 或者
[*] owner table_name
[*] */
[*] v_tablist [color=#00cc]:[color=#00cc]= metadata_ora2gp[color=#00cc].split[color=#00cc](in_tablist[color=#00cc])[color=#00cc];
[*]
[*] for loop_idx in 1 [color=#00cc].[color=#00cc]. v_tablist[color=#00cc].count loop
[*]
[*] with t as
[*] [color=#00cc](
[*] [color=#ff990]--check约束
[*] select [color=#ff0ff]'alter table ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]|[color=#00cc]|
[*] [color=#ff0ff]' add constraint ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].constraint_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]' check (' [color=#00cc]|[color=#00cc]|
[*] replace[color=#00cc](dc[color=#00cc].search_condition[color=#00cc],[color=#ff0ff]'"'[color=#00cc]) [color=#00cc]|[color=#00cc]| [color=#ff0ff]');' as cons_ddl
[*] from sys[color=#00cc].my_constraints dc
[*] where dc[color=#00cc].constraint_type in [color=#00cc]([color=#ff0ff]'C'[color=#00cc])
[*] and dc[color=#00cc].status [color=#00cc]= [color=#ff0ff]'ENABLED'
[*] and dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]= upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])
[*] union all
[*] [color=#ff990]--reference约束
[*] select [color=#ff0ff]'alter table ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]|[color=#00cc]|
[*] [color=#ff0ff]' add constraint ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].constraint_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]' foreign key(' [color=#00cc]|[color=#00cc]|
[*] collist [color=#00cc]|[color=#00cc]| [color=#ff0ff]') references ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].r_owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]|
[*] dc1[color=#00cc].table_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]';'
[*] from sys[color=#00cc].my_constraints dc[color=#00cc],
[*] [color=#00cc](select dcc[color=#00cc].owner[color=#00cc],
[*] dcc[color=#00cc].constraint_name[color=#00cc],
[*] dcc[color=#00cc].table_name[color=#00cc],
[*] listagg[color=#00cc](column_name[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by dcc[color=#00cc].position[color=#00cc]) as collist
[*] from dba_cons_columns dcc
[*] group by dcc[color=#00cc].owner[color=#00cc], dcc[color=#00cc].constraint_name[color=#00cc], dcc[color=#00cc].table_name[color=#00cc]) cons_col[color=#00cc],
[*] sys[color=#00cc].my_constraints dc1
[*] where dc[color=#00cc].constraint_type in [color=#00cc]([color=#ff0ff]'R'[color=#00cc])
[*] and dc[color=#00cc].status [color=#00cc]= [color=#ff0ff]'ENABLED'
[*] and dc[color=#00cc].owner [color=#00cc]= cons_col[color=#00cc].owner
[*] and dc[color=#00cc].table_name [color=#00cc]= cons_col[color=#00cc].table_name
[*] and dc[color=#00cc].constraint_name [color=#00cc]= cons_col[color=#00cc].constraint_name
[*] and dc[color=#00cc].r_owner [color=#00cc]= dc1[color=#00cc].owner
[*] and dc[color=#00cc].r_constraint_name [color=#00cc]= dc1[color=#00cc].constraint_name
[*] and dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]= upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])
[*] union all
[*] [color=#ff990]--unique约束
[*] select [color=#ff0ff]'alter table ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]|[color=#00cc]|
[*] [color=#ff0ff]' add constraint ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].constraint_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]' unique(' [color=#00cc]|[color=#00cc]|
[*] collist [color=#00cc]|[color=#00cc]| [color=#ff0ff]');'
[*] from sys[color=#00cc].my_constraints dc[color=#00cc],
[*] [color=#00cc](select dcc[color=#00cc].owner[color=#00cc],
[*] dcc[color=#00cc].constraint_name[color=#00cc],
[*] dcc[color=#00cc].table_name[color=#00cc],
[*] listagg[color=#00cc](column_name[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by dcc[color=#00cc].position[color=#00cc]) as collist
[*] from dba_cons_columns dcc
[*] group by dcc[color=#00cc].owner[color=#00cc], dcc[color=#00cc].constraint_name[color=#00cc], dcc[color=#00cc].table_name[color=#00cc]) cons_col
[*] where dc[color=#00cc].constraint_type in [color=#00cc]([color=#ff0ff]'U'[color=#00cc])
[*] and dc[color=#00cc].status [color=#00cc]= [color=#ff0ff]'ENABLED'
[*] and dc[color=#00cc].owner [color=#00cc]= cons_col[color=#00cc].owner
[*] and dc[color=#00cc].table_name [color=#00cc]= cons_col[color=#00cc].table_name
[*] and dc[color=#00cc].constraint_name [color=#00cc]= cons_col[color=#00cc].constraint_name
[*] and dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]= upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])
[*] union all
[*] [color=#ff990]--primary约束
[*] select [color=#ff0ff]'alter table ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]|[color=#00cc]|
[*] [color=#ff0ff]' add constraint ' [color=#00cc]|[color=#00cc]| dc[color=#00cc].constraint_name [color=#00cc]|[color=#00cc]| [color=#ff0ff]' primary key(' [color=#00cc]|[color=#00cc]|
[*] collist [color=#00cc]|[color=#00cc]| [color=#ff0ff]');'
[*] from sys[color=#00cc].my_constraints dc[color=#00cc],
[*] [color=#00cc](select dcc[color=#00cc].owner[color=#00cc],
[*] dcc[color=#00cc].constraint_name[color=#00cc],
[*] dcc[color=#00cc].table_name[color=#00cc],
[*] listagg[color=#00cc](column_name[color=#00cc], [color=#ff0ff]','[color=#00cc]) within group[color=#00cc](order by dcc[color=#00cc].position[color=#00cc]) as collist
[*] from dba_cons_columns dcc
[*] group by dcc[color=#00cc].owner[color=#00cc], dcc[color=#00cc].constraint_name[color=#00cc], dcc[color=#00cc].table_name[color=#00cc]) cons_col
[*] where dc[color=#00cc].constraint_type in [color=#00cc]([color=#ff0ff]'P'[color=#00cc])
[*] and dc[color=#00cc].status [color=#00cc]= [color=#ff0ff]'ENABLED'
[*] and dc[color=#00cc].owner [color=#00cc]= cons_col[color=#00cc].owner
[*] and dc[color=#00cc].table_name [color=#00cc]= cons_col[color=#00cc].table_name
[*] and dc[color=#00cc].owner [color=#00cc]|[color=#00cc]| [color=#ff0ff]'.' [color=#00cc]|[color=#00cc]| dc[color=#00cc].table_name [color=#00cc]= upper[color=#00cc](v_tablist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])
[*] and dc[color=#00cc].constraint_name [color=#00cc]= cons_col[color=#00cc].constraint_name[color=#00cc])
[*] select [color=#00cc]* bulk collect into v_conslist from t[color=#00cc];
[*]
[*] for loop_idx in 1 [color=#00cc].[color=#00cc]. v_conslist[color=#00cc].count loop
[*] dbms_output[color=#00cc].put_line[color=#00cc](v_conslist[color=#00cc](loop_idx[color=#00cc])[color=#00cc])[color=#00cc];
[*] end loop[color=#00cc];
[*] end loop[color=#00cc];
[*]
[*] end convert_constraint_ddl[color=#00cc];
[*]end metadata_ora2gp[color=#00cc];
[*][color=#00cc]/