Below mentioned is a beginners linux shell script which can be used to clear old data from configured oracle tables. This script is kept flexible where you can enter the required oracle db user credentials , connect string , the table_name , the field to look for and the no of days the data has to be retained.Copy the code below highlighted in blue , save it in a file and execute for output. Before you start
a) Configure the ORACLE software environment variables as per your installation - i.e the ORACLE_BASE , ORACLE_HOME and PATH
b) Configure the number of rules under variable TOTAL_TABLES
c) Configure the rules for deletion under TABLE_1,TABLE_2 so on till TABLE_n.
d) Example of TABLE_1 rule defined in this script - connects to database ORAINST1 using db username shareolite , password shareolite , selects the count of records in table EMPLOYEE_DET having RECORD_DATE older than 365 days.
e) For automated execution, schedule this script using linux cronjob service
######################################################################################################
# Configure Oracle environment variable values here.
# Ex : export ORACLE_BASE=/usr/oracle/
# Ex : export ORACLE_HOME=/usr/oracle/product/11gR2/db_1/
######################################################################################################
export ORACLE_BASE=/usr/oracle/
export ORACLE_HOME=/usr/oracle/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin/
######################################################################################################
# Configure total number of configuration entries here
# Syntax : TOTAL_TABLES=<Number>
# Example : TOTAL_TABLES=5
######################################################################################################
TOTAL_TABLES=3
######################################################################################################
# Configure Table details & record deletion interval here.
# Syntax : TABLE_<no>="<DisplayText>,<DBUser>,<DBPass>,<DBSID>,<TableName>,<DateField>,<DaysCount>,<RowFetch>
# Ex : TABLE_1="EMPTable,shareolite,shareolite,ORAINST1,EMPDATA_1,SALARY,0,5000
######################################################################################################
TABLE_1=Employee,shareolite,shareolite,ORAINST1,EMPLOYEE_DET,RECORD_DATE,365,5000
TABLE_2=Salary,shareolite,shareolite,ORAINST1,SALARY_DET,LAST_PAID,60,5000
TABLE_3=Accounts,shareolite,shareolite,ORAINST1,EMP_ACCOUNT,DAY,730,5000
############################ Configuration Ends here ##################################
######################################################################################################
# Script definition starts here - not to be modified.
######################################################################################################
RUNDATE=`date +%d%b%y`
rm -f /tmp/DBOldRecCleaner-$RUNDATE.txt
cat $0 |grep 'TABLE_' > /tmp/DBOldRecCleaner-$RUNDATE.txt
count=1
while [ $count -le $TOTAL_TABLES ]
do
present=`cat /tmp/DBOldRecCleaner-$RUNDATE.txt | grep "TABLE_$count" |grep -v '#' |grep -v '//' | wc -l`
if [ $present -eq 1 ]
then
DATA=`cat /tmp/DBOldRecCleaner-$RUNDATE.txt | grep "TABLE_$count" |grep -v '#' |grep -v '//' |awk -F '=' '{print $2}'`
echo -e "`date` - ====================================================================="
echo -e "`date` - Read config $DATA "
tabdesc=`echo $DATA |awk -F ',' '{print $1}'`
dbsuser=`echo $DATA |awk -F ',' '{print $2}'`
dbspass=`echo $DATA |awk -F ',' '{print $3}'`
dbssids=`echo $DATA |awk -F ',' '{print $4}'`
tabname=`echo $DATA |awk -F ',' '{print $5}'`
tabcolm=`echo $DATA |awk -F ',' '{print $6}'`
tabdays=`echo $DATA |awk -F ',' '{print $7}'`
tabrows=`echo $DATA |awk -F ',' '{print $8}'`
spoolfl="/tmp/$RUNDATE-$tabdesc.txt"
fetchdate=`date --date "$tabdays days ago" +%d-%b-%Y`
rm -f $spoolfl
totalcount=`sqlplus -silent $dbsuser/$dbspass@$dbssids << EOF
set pagesize 0 feedback off verify off heading off echo off;
spool $spoolfl
select count(*) from $tabname where trunc($tabcolm) < trunc(sysdate-$tabdays);
spool off
EOF`
if [ -f $spoolfl ]
then
echo -e "\c"
else
echo -e "`date` - Problem with DB connection while quering table - $tabdesc - $dbsuser/$dbspass@$dbssids"
exit
fi
oraerror=`grep "ORA-" $spoolfl |wc -l`
if [ $oraerror -gt 0 ]
then
echo -e "`date` - Getting oracle error for select query. Stopping execution. Please correct the config"
break
fi
totattempts=`echo $(($totalcount/$tabrows))`
attempt=1
echo -e "`date` - Total records - $totalcount ($fetchdate), Total delete attempts - $totattempts , FetchRowCount - $tabrows"
while [ $attempt -le $totattempts ]
do
echo -e "`date` - Table - $tabname , Attempt no. - $attempt - Deleting $tabrows rows"
sqlplus -silent $dbsuser/$dbspass@$dbssids << EOF
set pagesize 0 feedback off verify off heading off echo off;
delete from $tabname where trunc($tabcolm) < trunc(sysdate-$tabdays) and rownum < $tabrows;
commit;
EOF
attempt=`echo $(($attempt+1))`
sleep 0.5
done
else
echo -e "`date` - Config TABLE_$count is not present, please check & correct the config"
fi
count=`echo $(($count+1))`
done
exit
######################################################################################################
# Script definition ends here
######################################################################################################
Hope this is useful to some Linux beginners