oracle 定期迁移分区表数据（不落地）

[oracle@SJ ~]$ cat /home/oracle/JY_SJ.sh
#!/bin/bash
source /home/oracle/.bash_profile
SDATE=$(date  +%Y%m)
TDATE=$(date -d "-1 month" +%Y%m)
SPNAME="P"${SDATE}
TPNAME="P"${TDATE}
#@/home/oracle/truncate.sql

sqlplus / as sysdba > /home/oracle/SJ_PAR_`date +%y%m%d%H`.log<<EOF
alter session set container=SJDB;
set feedback off heading off term off
set pages 0 trim on trims on lines 32767 long 999999
set echo off
spool SJ_PAR.sql

select count(*) from SJ.T_TEST_INF_SJ partition ($SPNAME);  
select count(*) from SJ.T_TEST_INF_JY partition ($TPNAME);

alter table SJ.T_TEST_INF_SJ truncate partition $TPNAME;
alter table SJ.T_TEST_INF_JY truncate partition $SPNAME;

select count(*) from SJ.T_TEST_INF_SJ partition ($SPNAME);
select count(*) from SJ.T_TEST_INF_JY partition ($TPNAME);

spool off
exit
EOF
logname=`date +%y%m%d%H`
impdp \'sys/SJ@SJDB as sysdba\' content=data_only logfile=$logname.log directory=SJ tables=SJ.T_TEST_INF_SJ:$TPNAME,SJ.T_TEST_INF_JY:$SPNAME remap_tablespace=SS_data:SJ_data,SS_idx:SJ_idx remap_schema=SS:SJ network_link=SJ_JY

 

转载于:https://www.cnblogs.com/ss-33/p/8986596.html