批次轉換分割槽表為普通表

今天資料遷移的小組找到我，希望我能夠重新構建一些測試環境，其中測試環境中的一些分割槽表都需要去掉分割槽，轉換成普通表的形式，因為他們在做一些工作的時候碰到了問題，而且希望必要的約束等都保留，這個需求聽起來倒不復雜，很清晰，我看了下需要轉換的表，一看有將近100多個，而且重構好幾套環境，想想都頭疼。
這個需求是很特別，至少從資料庫層面是不支援的。
一種類似就是透過exp/imp 做資料結構的同步，生成對應的ddl語句，然後解析ddl語句,把分割槽的部分剔除。
因為exp生成的ddl語句含有很多的儲存細節,storage,segment_attributes等,解析的過程也更為複雜，不好控制。
還有一種思路就是透過dbms_metadata來生成ddl語句，先過濾一些不必要的ddl細節,然後在生成的ddl語句中剔除分割槽資訊。
這個部分相對來說要稍微容易一些。
可以透過如下的指令碼來生成不含有分割槽資訊的ddl語句。
大體思路就是先生成ddl語句，然後在關鍵字中擷取"PARTITION BY"之上的部分。

#chg_partition.sh

sqlplus -s n1/n1@testdb < select *from dual;
set linesize 300
set pages 0
col sql_text format a300
set feedback off
set long 99999
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); 
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true); 
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true); 
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',true); 
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false); 
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false); 
spool tmp_$1.sql
select 'select  DBMS_METADATA.GET_DDL('||chr(39)||'TABLE'||chr(39)||','||chr(39)||table_name||chr(39)||','||chr(39)||owner||chr(39)||') sql_text from dual; 'FROM all_tables where owner='N1' and table_name in
(
upper('$1')
);
spool off;
spool $1_ddl.tmp
@tmp_$1.sql
spool off
EOF

last_line=`grep -n "PARTITION BY"  $1_ddl.tmp|awk -F: '{print $1}'`
last_line=`expr $last_line - 1`
sed -n '1,'$last_line'p'   $1_ddl.tmp > tmp_$1_ddl.sql1
sed 's/PRDAPPO/'$2'/' tmp_$1_ddl.sql1 > tmp_$1_ddl.sql
echo ";" >>  tmp_$1_ddl.sql
rm $1_ddl.tmp
rm tmp_$1_ddl.sql1

如果我們指定需要處理的表為 CUSTOMER_INFO,處理之後生成的檔案就會使如下的樣子。

-rw-r--r-- 1 xxxxx  dba  5723 Sep 11 14:32 tmp_CUSTOMER_INFO_ddl.sql

如果目前有100多個表。
可以寫一個簡單的入口檔案來呼叫chg_partition.sh 即可,這樣不管有多少表需要轉換都可以一次搞定，最後生成一個run.sql的檔案，直接執行run.sql就可以建立所有的表了。
ksh chg_partition.sh MENT_RESOURCE            CONV_TEST     
ksh chg_partition.sh ES_HISTORY               CONV_TEST
ksh chg_partition.sh GED_TRIAL_BALANCE        CONV_TEST
ksh chg_partition.sh HARGES                   CONV_TEST
ksh chg_partition.sh HARGE_GROUP              CONV_TEST
ksh chg_partition.sh REDIT_DEBIT_LINK         CONV_TEST
ksh chg_partition.sh USTOMER_CREDIT           CONV_TEST
ksh chg_partition.sh NVOICE                   CONV_TEST
ksh chg_partition.sh AYMENT                   CONV_TEST
ksh chg_partition.sh AYMENT_ACTIVITY          CONV_TEST
ksh chg_partition.sh AYMENT_DETAILS           CONV_TEST
ksh chg_partition.sh AX_ITEM                  CONV_TEST
ksh chg_partition.sh RANSACTION_LOG           CONV_TEST
ksh chg_partition.sh NAPPLIED_CREDIT          CONV_TEST
ksh chg_partition.sh ILL_STATEMENT            CONV_TEST
ksh chg_partition.sh HARGE                    CONV_TEST
ksh chg_partition.sh USTOMER_INFO             CONV_TEST
ksh chg_partition.sh YCLE_CUSTOMERS           CONV_TEST
ksh chg_partition.sh YC_PAYER_POP             CONV_TEST
ksh chg_partition.sh OCUMENT                  CONV_TEST
ksh chg_partition.sh NVOICE                   CONV_TEST
ksh chg_partition.sh NV_CHARGE_REL            CONV_TEST
ksh chg_partition.sh C_RATES                  CONV_TEST
ksh chg_partition.sh AX                       CONV_TEST
ksh chg_partition.sh AX_ITEM                  CONV_TEST
ksh chg_partition.sh REATMENT_ACTIVITY        CONV_TEST
ksh chg_partition.sh GREEMENT_PARAM           CONV_TEST
ksh chg_partition.sh GR_PRM_HISTORY           CONV_TEST
ksh chg_partition.sh R_GRP_MEMBERS            CONV_TEST
ksh chg_partition.sh EMO                      CONV_TEST

ls -lrt *.sql |awk '{print "@"$9}' > run.sql