Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Is it just me

Re: Is it just me

From: Lisa Spory <lspory_at_yahoo.com>
Date: Wed, 11 Aug 2004 10:22:29 -0700 (PDT)
Message-ID: <20040811172229.5419.qmail@web53207.mail.yahoo.com>


Conner / Nuno,  

I am in the process of designing a audit capability myself and was considering a single table approach. In fact, Tom Kyte has an example of a single table approach on AskTom that I have found to be pretty nifty.  

http://asktom.oracle.com/pls/ask/f?p=4950:8:18330715232256760352::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:59412348055,  

Justification for a single table:  

I can create a configuration table with a list of tables/columns to be audited. Based upon changes to this table, I can dynamically generated new triggers. Our system aggressively pushes out new releases, most of which will introduce new data elements to be audited (and new columns to already audited tables). Therefore, an approach that logs each table history is a custom table will impose a huge sustainment burden.  

The trigger code calls the same package procedure regardless of table because the implementation of logging the audit is the same for every table and column. This allows me to auto-generate the triggers and maintain a single package of audit code.  

The reading of the system audit log will be very infrequent (1-2 times a year) and only supported through a formal customer request. Therefore, I had planned on partitioning the audit table by timestamp and sliding out data pretty frequently. Depending upon the report request, I figure that the required archived partitions could be restored in another location, and manipulated in any way to support the specific request.  

Thoughts? Comments?  

Also - Enjoy a Violet Crumble and some Chicken Twisties for me :-) I lived in WA from 1988-1992 and haven't had a chance to make it back yet, sure do miss it...  

Nuno Souto <dbvision_at_optusnet.com.au> wrote: Connor McDonald apparently said,on my timestamp of 11/08/2004 11:30 PM:
> Its like the "audit" facility on a system I've recently seen. ins/upd/del on every single table -
> every single row change logged to a single audit table which (in order to handle all tables) has
> columns (renamed to preserve anonymity)
>

:) I can see we are in the same country! I just finished one that originally had a single audit table. All ins/upd/del PLUS all sel were logged!!!!!!!!! Table, data, user, function and screen used. Unreal.

DB was nearing 100Gb, real data was about 35Gb. Needless to say, the deranged idea got knocked on the head in the new design. Still, took a while to convince them it was ludicrous! Previous Access users...

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_optusnet.com.au
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

		
---------------------------------
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Aug 11 2004 - 12:19:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US