Re: Audit Purge Script

From: Nagaraj S <nagaraj.chk_at_gmail.com>
Date: Fri, 30 Mar 2012 03:55:50 +0530
Message-ID: <CAG6s0C0Sc3H4L13KgPnwzcK7zNrgzCc0grS91rcCOhT8cAH-iA_at_mail.gmail.com>

Thank You i got it. I use below procedure to schedule the job and my understanding is
 job_action => 'begin purge_audit_trail(90); end;', --> the purge will keep only 90 days record. Plz correct me if i am wrong

BEGIN
  sys.dbms_scheduler.create_job(

    job_name => 'AUDIT_PURGE',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin purge_audit_trail(90); end;',

    schedule_name => 'MAINTENANCE_WINDOW_GROUP',     job_class => '"DEFAULT_JOB_CLASS"',
    comments => 'Audit Trail Purge',
    auto_drop => FALSE,
    start_date => SYSTIMESTAMP,
    repeat_interval => 'SYSTIMESTAMP + 1 /* 1 Day */');     enabled => TRUE);
END;
/

On Fri, Mar 30, 2012 at 3:39 AM, kathryn axelrod <kat.axe_at_gmail.com> wrote:

> But at this creation point, what is 90 or 91 to this package? It's
> meaningless.
>
> First you have to create the package saying in essence, when running
> this package, there will be an incoming (number) value. Then, when you
> actually run the package is when you can give it that value.
>
> So, create the package (using the syntax below *exactly* as is):
>
> create or replace procedure purge_audit_trail (days in number) as
> purge_date date;
> begin
> purge_date := trunc(sysdate-days);
> dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
> purge_date || ' started');
> delete from aud$ where ntimestamp# < purge_date;
> commit;
> dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
> purge_date || ' has completed');
> end;
> /
>
>
>
> And then run it:
>
> exec purge_audit_trail(90);
>
>
>
> On Thu, Mar 29, 2012 at 3:02 PM, Nagaraj S <nagaraj.chk_at_gmail.com> wrote:
> > yeah your right. i using the script from
> >
> http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/
> .
> > However when i gave 90 it gave me error
> >
> >> SQL> create or replace procedure purge_audit_trail ('90') as
> ..
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Mar 29 2012 - 17:25:50 CDT