drop partition using shell and sql (2 shell scripts)

We have a partitioned table with partitions like

zzt_service_log_2015MAY
zzt_service_log_2015APR
zzt_service_log_2015JUN

this table growth is huge and we dont need older data. So we need to drop partitions every month to reclaim space.

So my plan is to write a script and run it on 1st of every month so it will delete the old partitions.

Say for example if today is may 1st we will retain april partition and delete march partition.

So  since only last 3 letters of my partitions are changing i used dual from sql to get the month.

I used sysdate-45 and run on first of every month so it will return the month before previous month. We can do this using shell as well but i used sqlplus for this.

here is the command for that

SELECT TO_CHAR(SYSDATE-45,'MON') FROM DUAL;

The above will return 3 letters of month name in Uppercase which we use in partitions.

So first i tried in staging and here is the script for that. Worked like charm and no issues

#!/bin/ksh
export ORACLE_SID=shsub
export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4
count=$(sqlplus -s '/@shsub_sys as sysdba'<<EOF
  set pages 0 feed off
  SELECT TO_CHAR(SYSDATE-45,'MON') FROM DUAL;
EOF
)
sqlplus /@shsub_sys as sysdba <<EOF
alter table hma_tm_prod_gen2_monitor.zzt_service_log drop partition zzt_service_log_2015$count update global indexes;
exit
EOF

Then i tried in production and i got so many errors. Server settings may be? So with the help of my friend we wrote another script for production and this is how scripts look like

#!/bin/ksh

export ORACLE_SID=phsub1

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/11.2.0.4

function run_one

{

sqlplus -s "/ as sysdba" << EOF

set heading off;

SET pages 0;

SET echo off;

SET feed off;

SELECT TO_CHAR(SYSDATE-45,'MON') FROM DUAL;

quit

EOF

}

count=`run_one`

sqlplus -s "/ as sysdba" <<EOF

alter table hma_tm_prod_gen2_monitor.zzt_service_log drop partition zzt_service_log_2015$count update global indexes;

exit

EOF

Then i setup a cron to run on 1st of everymonth. All set :)