分区表转普通表脚本

用法：./xxx.sh tablename username

xxx.sh:

sqlplus -s test/oracle_4U <<EOF

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 in 
( 
upper('$2') 
) 
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.sql
echo ";" >>  tmp_$1_ddl.sql
rm $1_ddl.tmp
rm tmp_$1.sql