day13_普通表转换成分区表——T_MOBBIZ_LOG表清理
------------批量删除旧数据--------------
mkdir -p /yh_exp/cron
chmod 777 /yh_exp/cron
mkdir -p /home/oracle/awr_rpt/cron
chmod 777 /home/oracle/awr_rpt/cron
mkdir -p /yh_exp/cron/cronlog/
chmod 777 /yh_exp/cron/cronlog/
cd /home/oracle/awr_rpt/cron
vi DEL_T_MOBBIZ_LOG.sh
#!/bin/sh
DATE="`date +%Y-%m-%d`"
EXP_D="/yh_exp/cron"
ORA_BIN="/oracle/app/oracle/product/10.2.0/db_1/bin"
${ORA_BIN}/sqlplus scott/lipengfei @${EXP_D}/DEL_T_MOBBIZ_LOG.sql >/yh_exp/cron/cronlog/DEL_T_MOBBIZ_LOG${DATE}.log 2>&1
cd /yh_exp/cron
vi DEL_T_MOBBIZ_LOG.sql
set serveroutput on size 100000;
prompt Clear T_MOBBIZ_LOG data
BEGIN
LOOP
delete T_MOBBIZ_LOG
where QUERY_TIME < to_date('2014-09-01','yyyy-mm-dd')
AND ROWNUM<10000;
EXIT WHEN SQL%NOTFOUND;
COMMIT;
dbms_output.put_line('commit 10000');
END LOOP;
END;
/
commit;
exit;
EOF
nohup sh /home/oracle/awr_rpt/cron/DEL_T_MOBBIZ_LOG.sh &
