flemingdg and I work for the same company. We install Oracle at multiple remote sites, we turn on Archive log and instruct the local admins to purge the archive log files on a regular basis. They don't always do that. We are trying to create some kind of procedure to automatically delete the archive logs that are older then 30 days. We would want to run some kind of pl/sql procedure on a regular interval which would look at the right table, delete the archive log and maybe even the record pointing to the file. I know that pl/sql is limited in file handleing so I thought about writing a dos command file that accepted a file name as an input and deleted it. But I'm not sure how to run an external command in pl/sql. Any thoughts or help on managing this process would be appreciated.
You have not mentioned about backing up them before deletion.
I have two solutions to yr problem.
1. If you have Veritas Backup Exec, there is a File Grooming feature which takes the backup of selected files and then deletes them from the server. You can define number of days and other options.
2. I have got following script to delete Archive log files which are older than n number of days but it does not have any mechanism of backing them first.
Here's a way to delete your archive log files automatically.
This script deletes archive logs that are more than 30 days old.
It is very useful when you need to clear some disk space.
Of course, make sure that you have valid backups or you risk deleting archives that you
First, the script gives a grant to objects owned by the user sys.
Then, it creates a directory to use the bfilename function. Finally,
it creates two procedures: One that extracts the name of the archive and another
that finds old archive logs and deletes them.
You can execute this procedure manually, or create a job that will execute it periodically.
grant select on v_$archived_log to YOUR_USER;
grant execute on dbms_backup_restore to YOUR_USER;
create or replace directory ARCHIVEDIR as
create or replace function proc_nom_fich(P_chain IN Varchar2)
return varchar2 IS
l_posi := length(P_chain);
if substr(P_chain,l_posi,1) in ('/','\') then
l_nom_fich := substr(P_chain,l_posi + 1);
l_posi := l_posi - 1;
if l_posi < 0 then
create or replace procedure proc_dele_arch_log is
cursor sel_archive is
where completion_time < sysdate - 30;
for list in sel_archive loop
arch_exis := FALSE;
arch_file_name := proc_nom_fich(list.name);
arch_file := bfilename('ARCHIVEDIR',arch_file_name);
arch_exis := dbms_lob.fileexists(arch_file) = 1;
if arch_exis then
select sys.jobseq.nextval into l_job_nb from dual;
to_date(sysdate,'DD-MON-YYYY HH24:MI'), 'sysdate + 7');
To handle yourself, use your head. To handle others, use your heart
RMAN is preferred by Oracle over the Hot Backup method because it generates less redo in the database and you can also throttle the amount of I/O used by RMAN. (I have implemented this functionality on my scripts.) This means that if necessary, you can perform the RMAN backup while users are actively using the server without consuming all of the I/O bandwidth of the server. You will need to adjust the default I/O rate to a value which is suitable for your server.