自动生成导出分区表脚本

脚本如下，改自ORACLE DBA工作笔记：

parallel_no=$4 
target_schema=$3 
for ((i=1;i<$parallel_no;i++))
do
sqlplus -s $1 <<EOF
set linesize 150 
set pages 0 
set feedback off 
set trimspool  on 
spool $2_partition_parallel_$i.par_temp 
select 'tables=' from dual; 
spool off; 
spool $2_partition_parallel_$i.par_temp append 
 select table_name||':'||partition_name||',' from ( 
 select rownum,mod(rownum,${parallel_no})+1 parallel_no,table_name,partition_name,num_rows from user_tab_partitions where table_name=upper('$2') order by num_rows desc  nulls last 
) 
where parallel_no=${i}; 
spool off; 
spool $2_partition_parallel_${i}.par_temp2 
select 'tables=' from dual; 
spool off; 
spool $2_partition_parallel_${i}.par_temp2 append 
 select 'PRDAPPO'||'.'||table_name||':'||partition_name||',' from ( 
 select rownum,mod(rownum,${parallel_no})+1 parallel_no,table_name,partition_name,num_rows from user_tab_partitions where table_name=upper('$2') order by num_rows desc  nulls last 
) 
 where parallel_no=${i}; 
spool off;
EOF
sed -e '/^$/d' -e '$s/.$//'  $2_partition_parallel_${i}.par_temp>$2_partition_parallel_${i}.par_imp 
rm $2_partition_parallel_${i}.par_temp 
sed -e '/^$/d' -e '$s/.$//'  $2_partition_parallel_${i}.par_temp2>$2_partition_parallel_${i}.par_impdp 
rm $2_partition_parallel_${i}.par_temp2 
echo exp $1 file=par${i}_$2.dmp log=par${i}_$2_exp.log  statistics=none grants=n constraints=n indexes=n  parfile=$2_partition_parallel_${i}.par_imp buffer=9102000  "&" >>$2_partition_parallel_export.sh 
echo imp $1 file=par${i}_$2.dmp log=par${i}_$2_imp.log  statistics=none grants=n constraints=n indexes=n  parfile=$2_partition_parallel_${i}.par_imp buffer=91020000 ignore=Y commit=y "&" >>$2_partition_parallel_import.sh 
echo expdp $1 directory=memo_dir dumpfile=par${i}_$2.dmp logfile=par${i}_$2_expdp.log parfile=$2_partition_parallel_${i}.par_imp  exclude=statistics,constraint,ref_constraint,index,comment,grant  "  &" >>$2_partition_parallel_expdp.sh & 
echo "nohup " impdp $1 directory=memo_dir dumpfile=par${i}_$2.dmp parallel=2 include=table_data logfile=par${i}_$2_impdp.log parfile=$2_partition_parallel_${i}.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS   "  &" >>$2_partition_parallel_impdp.sh &
done

使用命令

bash ./xxx.sh user/password tablename username parallel

bash ./cut.sh test/oracle_4U pdba test 4