今天数据迁移的小组找到我，希望我能够重新构建一些测试环境，其中测试环境中的一些分区表都需要去掉分区，转换成普通表的形式，因为他们在做一些工作的时候碰到了问题，而且希望必要的约束等都保留，这个需求听起来倒不复杂，很清晰，我看了下需要转换的表，一看有将近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

ITPub博客

批量转换分区表为普通表

最新文章