Re: Is is possible to range partition sys.aud$ table ...

From: David Fitzjarrell <fitzjarrell_at_cox.net>
Date: 7 Jul 2004 14:28:34 -0700
Message-ID: <9711ade0.0407071328.59078493@posting.google.com>

premmehrotra_at_hotmail.com (Prem K Mehrotra) wrote in message news:<43441e77.0407070424.396afe1b_at_posting.google.com>...
> Even though not supported by Oracle, people move sys.aud$ table to a
> different tablespace (as dicussed in this news group as well as in
> some books). My
> question is, is it also possible to partition (range) this table based
> on timestamp or so. I have to keep seven years worth of audit data
> and I wanted
> to keep each quarter's audit data in a separate partition. I will
> think it is
> risky to partition the aud$ table because aud$ table is created by
> Oracle but just
> wanted to get other's opinions If I cannot partition the aud$ table, I
> was thinking of creating my own table and then have a job to
> periodically l move data from aud$ to my table.
>
>
> Thanks,
>
>
> Prem

It's possible, but I wouldn't do it. Oracle won't support moving SYS.AUD$ to another tablespace even though Metalink provides a

document to explain how to do it, so I'm certain Oracle won't support
range-partitioning this table.  I didn't go far enough with my answer
to the last poster asking about doing 'unspeakable' things to

SYS.AUD$, and I was called on it rather quickly. :)

I doubt that Oracle would support placing a trigger on SYS.AUD$, either.

So, this leaves you with the option of creating a range-patitioned copy of SYS.AUD$, in some other schema most likely, to house your seven years of audit data. You could then write a procedure and schedule it with DBMS_JOB to move data from SYS.AUD$ to your range partitioned copy, as you stated. Performing this task on a weekly basis should transfer data at a sufficiently fast rate (being you only have a week of audit records to copy) to ensure your scheduled job isn't tying up resources for a prolonged period. If you can load your copy with the currently accumulated data in one go (say, on a weekend when the system isn't busy) you may be able to keep just the current week of records in SYS.AUD$ and, come Sunday (which is when I usually schedule weekly tasks) you can copy the data to your partitioned table then truncate SYS.AUD$, a faster operation than delete. I can't see why that wouldn't be supported by Oracle, since you're not directly modifying the SYS.AUD$ table in any way other than deleting data from it in a timely fashion. Of course, if you're storing seven years of audit data I'd put this range-partitioned copy in its own tablespace, and put nothing else there.

David Fitzjarrell Received on Wed Jul 07 2004 - 16:28:34 CDT