Deleting Old Archive Log Files

[ronnie]

Hi,

How do schedule a job to delete old archive log files. say for example all archive log files older than 3 days.

I have a Windows 2000 server.

Thanks

[puneet]

*******************************************************************************
Rem This procedure creates a command file in C:\SCRIPTS called
Rem 'arch_del.cmd' which can be scheduled to execute by using the
Rem AT command in Windows NT. This procedure is submitted as
Rem a batch job which is scheduled once a week and deletes old
Rem archives.

Rem This procedure is designed for Oracle version 7.x. For Oracle8,
Rem check trunc(time) < trunc(sysdate)-10, where time is in the DATE
Rem field and the table name is V$ARCHIVED_LOG. In version 7.x,
Rem time is VARCHAR2. The procedure uses a pre-defined package
Rem called UTL_FILE. The init.ora should be modified to include
Rem a parameter called UTL_FILE_DIR=C:\SCRIPTS so that it can
Rem write in that destination directory.

*******************************************************************************
CREATE OR REPLACE PROCEDURE sp_arch_delete AS

arch_name UTL_FILE.FILE_TYPE;

arch_file varchar2(100);

BEGIN

DECLARE CURSOR c1 IS SELECT 'del '||name name
FROM V$ARCHIVED_LOG
WHERE trunc(completion_time) < TRUNC(sysdate)-10;

BEGIN

arch_name := UTL_FILE.FOPEN('C:\SCRIPTS','arch_del.cmd','w');

FOR rec IN c1

LOOP

arch_file := rec.name;

UTL_FILE.PUT_LINE(arch_name,arch_file);

END LOOP;

UTL_FILE.FCLOSE(arch_name);

END;

END;

/

*********************************************************************************************
Rem The following DBMS package procedure submits the batch job to execute
Rem the above procedure once every week. The job is submitted by connecting
Rem as internal through the Server Manager. The job executes the procedure
Rem at 7:00 p.m. on the day you submit the job and 7:00 p.m. at the interval of
Rem seven days.
********************************************************************************************
SVRMGR> VARIABLE jobno number;

SVRMGR> begin

2> EXECUTE DBMS_JOB.SUBMIT(:jobno,

3> 'sp_arch_del;',trunc(sysdate)+19/24,'(trunc(sysdate+19/24)+7');

4> end;

5> /
*********************************************************************************************
Rem The following AT command executes F:\SCRIPTS\arch_del.cmd every
Rem Monday night at 8.00 p.m and creates a log file at F:\SCRIPTS\arch_del.log.
*********************************************************************************************
F:> at 20:00 /every:M "C:\SCRIPTS\arch_del.cmd 1>C:\SCRIPTS\arch_del.log 2>&1"

[puneet]

Thanks

[khussain]

There is no way to automate this thru Oracle.

You must write a script to do this, and schedule it thru 'AT' - windows scheduler.

I have a script that deletes the files after the cold backup.

[pando]

Originally posted by khussain
There is no way to automate this thru Oracle.

You must write a script to do this, and schedule it thru 'AT' - windows scheduler.

I have a script that deletes the files after the cold backup.

there is way to do it in Oracle

check
http://www.dbasupport.com/forums/sho...threadid=18680

[ronnie]

Originally posted by pando

Originally posted by khussain
There is no way to automate this thru Oracle.

You must write a script to do this, and schedule it thru 'AT' - windows scheduler.

I have a script that deletes the files after the cold backup.

there is way to do it in Oracle

check
http://www.dbasupport.com/forums/sho...threadid=18680

Pando,

Clicking on the Link takes me to dbasupport home page.

Thanks

[pando]

http://www.dbasupport.com/forums/sho...threadid=18680