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: Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl>
Date: Thu, 12 Aug 2004 15:19:39 +0200
Message-Id: <1092316779.6005.71.camel@dbalert199.dbalert.nl>


Hi Lisa,
Although you said you can't update to 10g yet, I just got a brainfart that _might_ be helpful in a 10g environment and can be adopted to 9i as well. I have not tested this, so I cannot go into detail about all consequences, but by chance I will elaborate on the idea some time in the future. Of course, I encourage anyone to do this testing and share the results.

When enabling flashback, you are able to peek into old and new values of a column in the (near) past using flashback queries. The trigger for the logging can just push a message in a queue, containg the user involved, table, rowid and timestamp or SCN. The job handling the queue will retrieve old and new values using flashback, and write the proper audit trail info into the logtable. Of course one of the assumptions is that the serialization isn't moved from the insert in the logtable to the adding of the message to the queue. Furthermore, the savings on the inserts in the logtable have to outweigh the cost of the extra I/O involved with flashback, and the processing of the queue. The whole idea is just making the logging asynchronous, without risking the loss of information. AQ can be of great help doing that. I think that adding the extra info of columns and old/new values to the message might make this working for 9i as well. Of course I would only go this way when the logging becomes a bottleneck. When the system works fine, don't change it. I wouldn't encourage you to introduce more complexity, although I'm a director of a (small) Oracle consultancy company as well ;-).

Just my $0.02.

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok)

On Thu, 2004-08-12 at 14:45, Lisa Spory wrote:

> Actually, Tom's example is data audit logging, his table structure defines who, table_name, column_name, and new and old values.
>
> > really don't like the idea of *extensive* logging into a single
> table: there is a bottleneck-waiting-to-happen, if I ever heard of 1...
>
> I see your point here, however, can't this be addressed via freelists and initrans settings?
>



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 Thu Aug 12 2004 - 08:10:36 CDT

Original text of this message

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