CODEWITHSUNDEEP
oracle-databaseplsql
Khallas301
Khallas301

Reputation: 61

tricky plsql procedure to delete data

Friends and pl/sql gurus...

I am trying to create procedure to delete data from audit table but don't know where to start with..

it will be great if somebody could provide some tips or pointer...

Requirements:

  1. Procedure will run on first Saturday of the month (not sure is it possible via pl/sql or have to create separate job)

  2. Delete data older than 2 months from Mon - Sat and date shouldn't be 1st of the month. (i.e. leave Sunday & 1st of the month data older than 2 months)

e.g.

*Procedure delete_log

IS

BEGIN


delete from audit_logs
where created >= trunc(sysdate - 60) and created < trunc(sysdate)
and created != (Sunday)
and created != (First of the month);

Commit;

End delete_log;*

I don't have much experience with pl/sql so all tips are appreciated..

Thanks, Mike

Upvotes: 0

Views: 2025

Answers (2)

David Aldridge
David Aldridge

Reputation: 52336

The DBMS_Scheduler calendaring syntax will support running a process on the first saturday of the month.

The syntax would be something like:

'FREQ=MONTHLY; BYDAY=SAT; BYSETPOS=1'

You'd add clauses for the exact time at which you want it to run.

'FREQ=MONTHLY; BYDAY=SAT; BYHOUR=3; BYMINUTE=30; BYSECOND = 0; BYSETPOS=1'

As far as the delete is concerned:

delete from audit_logs
where created                   <  add_months(trunc(sysdate,'MM'),-2)   and
      to_char(created,'DY')     != 'SUN'                                and
      extract(DAY from created) != 1;

Upvotes: 1

verbanicm
verbanicm

Reputation: 2526

To schedule a job you can use a cron job at the server level or Oracle Scheduler to run tasks http://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse002.htm#i1006395

Something like this would work:

CREATE OR REPLACE PROCEDURE delete_log
IS
BEGIN
   DELETE FROM audit_logs
         WHERE     TRUNC (created) < ADD_MONTHS (TRUNC (SYSDATE), -2)
               AND TO_CHAR (TRUNC (created), 'DY') != 'SUN'
               AND TO_CHAR (TRUNC (created), 'DD') != '01';
END;
/

Upvotes: 2

Related Questions