News

  1. Home
  2. All posts
All posts, Oracle by Team Ajara

Purge database recycle bin in oracle database

Purge database recycle bin in oracle database

 

export ORACLE_BASE=/u00/app/oracle
export ORACLE_HOME=/u00/app/oracle/product/19000/db_1
export PATH=${ORACLE_HOME}:${ORACLE_HOME}/bin:$PATH:.
export LOG_FILE=/u00/app/oracle/scripts/purge_recyclebin.logecho “Purge database recycle bin”

echo ORADW

 

sqlplus -s /nolog << eof
connect / as sysdba
spool $LOG_FILE
PURGE DBA_RECYCLEBIN;
spool off
exit
eof

err_cnt=`cat ${LOG_FILE} | grep “ORA-” | wc -l`

if [ ${err_cnt} -ge 1 ]
then
mail -s “ORADW: PURGE RECYCLEBIN ERROR” DBA@ajara.tech < ${LOGFILE}
fi

rm $LOG_FILE

Same script can be run on multiple databases after adding ” for loop” and modifying some lines  as mentioned below

ORCLD==> Dev database,   ORCLT ==> Test database , ORCLU ==> SIT database.

export ORACLE_BASE=/u00/app/oracle
export ORACLE_HOME=/u00/app/oracle/product/19000/db_1
export PATH=${ORACLE_HOME}:${ORACLE_HOME}/bin:$PATH:.

for DB in ORCLD  ORCLT  ORCLU

do

export LOG_FILE=/u00/app/oracle/scripts/${DB}_purge_recyclebin.log

echo “Purge database recycle bin”

echo $DB

sqlplus -s /nolog << eof
connect / as sysdba
spool $LOG_FILE
PURGE DBA_RECYCLEBIN;
spool off
exit
eof

err_cnt=`cat ${LOG_FILE} | grep “ORA-” | wc -l`

if [ ${err_cnt} -ge 1 ]
then
mail -s “$DB: PURGE RECYCLEBIN ERROR” DBA@ajara.tech < ${LOGFILE}
fi

rm $LOG_FILE

done

See also