CNDBA社区
# cat chg_partition.sh
sqlplus -s system/oracle@orcl <<EOF
select * from dual;
set linesize 300
set page 0
col sql_text fro 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,'STORAGE','PRETTY',true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','SQLTERMINA',true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','TABLESPACE',true);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','SEGMENT_ATTRIBUTES',false);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','REF_CONSTRAINTS',false);
spool tmp_$1.sql
select 'select DBMS_METADATA.GET_DDL('||chr(39)||'TABLE'||chr(39)||','||''''||table_name||chr(39)||','||''''||owner||chr(39)||') sql_text from dual;'
FROM all_tables where owner='SCOTT' 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 -nn '1,'$last_line'p' $1_ddl.tmp > tmp_$1_ddl.sql1
sed 's/PRADAPPO/'$2'/' tmp_$1_ddl.sql1 > tmp_$1_ddl.sql
echo ";" >> tmp_$1_ddl.sql
rm $1_ddl.tmp
rm tmp_$1_ddl.sql1
使用：
# ksh chg_partition.sh EMP
生成的文件如下
tmp_EMP_ddl.sql
分区表，普通表
