Arun, Here are a couple of files (a .bat and .sql) that let me maintain a constant number of Archived Redo Logs online.
The first batch file executes SQL*Plus to produce two other batch files to delete the excess logs and move some others, maintaining, in this case about 450 logs. It ran every hour - smooth as silk. Hope it helps. Archived_Redo_Logs_MoveDelete_main.bat ----------------------------------------------------------------------- Set ORACLE_SID=orcl SQLPlus internal @C: \Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_sub.sql Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat Call C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat ------------------------------------------------------------------------ Archived_Redo_Logs_MoveDelete_sub.sql ------------------------------------------------------------------------ Set FeedBack Off Set LineSize 200 Set PageSize 0 Set TrimSpool On -- Delete backup archived redo logs if there are over 450 total arc`d logs. Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_delete.bat Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) || '.LOG' >From ( Select To_Char(Sequence#,'09999') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum < 601 Minus Select 'Del T:\Oracle\OraData\orcl\Archive_Backup\ARC' || Trim(v.Seq) || '.LOG' >From ( Select To_Char(Sequence#,'09999') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum < 451 ; Spool Off -- Move some arc`d redo logs to backup if there are over 200 in Archive directory. Spool C:\Oracle\Admin\orcl\Archived_Redo_Logs_MoveDelete_move.bat Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T: \Oracle\OraData\orcl\Archive_Backup' >From ( Select To_Char(Sequence#,'09999') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum < 401 Minus Select 'Move U:\Oracle\OraData\orcl\Archive\ARC' || Trim(v.Seq) || '.LOG T: \Oracle\OraData\orcl\Archive_Backup' >From ( Select To_Char(Sequence#,'09999') Seq From v$Log_History Order By Sequence# Desc ) v Where RowNum < 201 ; Spool Off Exit ------------------------------------------------------------------------ Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 [EMAIL PROTECTED] Arun Chakrapani To: Multiple recipients of list ORACLE-L <ArunC@1800FLO <[EMAIL PROTECTED]> WERS.com> cc: Sent by: Subject: Script for deleting old archive logs [EMAIL PROTECTED] from NT om 05/15/2002 04:43 PM Please respond to ORACLE-L Does anybody has script to delete old archive logs on NT when the disk reaches certain percentage. Please let me know Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Arun Chakrapani INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).