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

Home -> Community -> Usenet -> c.d.o.server -> Re: NONE

Re: NONE

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sun, 21 Jul 2002 14:35:55 +1000
Message-ID: <ahddmp$2o0$1@lust.ihug.co.nz>


There isn't one, really... if there was a dictionary table capable of doing that at the row level, it would have to contain an entry for every row in every table. Doesn't bear thinking about.

In 9i, you can switch on monitoring for a table, and that tells you that something has happened to the contents of the table (a flag is set, used by dbms_stats for the purposes of statistics collection, but you could use it for your own ends). But even that's not row-specific (ie, you can't tell *what* has changed in a table).

Triggers are the only mechanism to do row-level (and thus value-based) auditing you seem to be after. There is a performance impact from using them, of course. How bad it would be would depend on how many tables you are planning to do this too, and what the transaction load on them was.

An alternative might be to do something clever by way of routine and automated monitoring of Log Miner, since the redo stream contains details of all regular DML that affects each table, including timestamp, username, O/S username, the nature of the transaction and the values altered. Log Miner was introduced in 8i, but can be used to read 8.0 logs. Not quite what you were after, though, I expect.

Regards
HJR "Bob Kosman" <kos_at_dtransform.com> wrote in message news:1103_1027224714_at_netnews.attbi.com...
> [For Oracle 8 and beyond:]
> Does anyone know of any way to detect that a row in a table has
> changed - that is, has been added or updated WITHOUT putting a
> trigger on the table? I had hoped that one of the data dictionary
> tuples might have such but have been able to find one.
>
> And failing this, just how much Oracle system overhead might there
> be if I did put add/update/delete triggers on all the tables for
> which I have an interest?
>
> Thanks.
>
> Kos.
>
>
Received on Sat Jul 20 2002 - 23:35:55 CDT

Original text of this message

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