oracle 定期copy 大表统计信息（分区表）

[oracle@test ~]$ cat /home/oracle/copy_statics.sh
#!/bin/bash
source /home/oracle/.bash_profile
SDATE=$(date  +%Y%m)
TDATE=$(date -d 'next-month' +%Y%m)
SPNAME="P"${SDATE}
TPNAME="P"${TDATE}
#echo $SPNAME
#echo $TPNAME
exec >> /home/oracle/copy_statics`date +%y%m%d%H`.log
sqlplus / as sysdba << EOF
set timing on
EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('TEST','T_TEST_TR_TEST');
EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('TEST','T_TEST_TR_TEST_EX_TEST');
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_CUP_Z_TR_TEST', '$SPNAME', '$TPNAME',FORCE=>TRUE);
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_TR_TEST', '$SPNAME', '$TPNAME', FORCE=>TRUE);
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_TR_TEST_EX_TEST', '$SPNAME', '$TPNAME', FORCE=>TRUE);
EXEC DBMS_STATS.LOCK_TABLE_STATS ('TEST','T_TEST_TR_TEST');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('TEST','T_TEST_TR_TEST_EX_TEST'); 
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_PSMTCK_TEST', '$SPNAME', '$TPNAME', FORCE=>TRUE);
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_HE_TR_TEST', '$SPNAME', '$TPNAME',FORCE=>TRUE);
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_SIG_TEST', '$SPNAME', '$TPNAME', FORCE=>TRUE);
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_ESIG_TEST', '$SPNAME', '$TPNAME', FORCE=>TRUE);
EXEC DBMS_STATS.COPY_TABLE_STATS ('TEST', 'T_TEST_CUP_TRA_TEST', '$SPNAME', '$TPNAME', FORCE=>TRUE);
exit;
EOF

 

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