Re: Maybe very silly question abou Log Miner

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<IlVga.1569$1s1.14787_at_newsfeeds.bigpond.com>...
> What you want are "supplemental log groups" -which, despite their name, have
> nothing to do with adding extra log groups as in 'files', but is merely a
> request to include primary key, unique key or any other combination of
> columns that take your fancy, within the redo stream, even if the DML
> statements haven't actually modified them.
>
> The command is something along the lines of 'alter table emp add
> supplemental log group (empno, ename, sal);' -but I don't have my Big Boy's
> Book of Syntax handy to check for sure. You can also do things like 'alter
> table emp add supplemental data (primary key)'. And, if utter madness
> strikes, you can always do 'alter database add supplemental data (primary
> key)'.

I've already found this. According to rule 'if something bug you in 8i, look in Oracle 9i new features and it will be corrected there'. :-) OK, no way do this in 8i, another reason for upgrade. I will probably try to use table level suplemental logging on a key column. As a key column I mean a column which is 'primary key' on table (for account table it can be account_id, for user table user_id etc.). I've gave commas around primary key, because we're not living in a perfect world and 'a primary key' may not be always the primary key. Maybe I can use a term 'natural primary key'.

> Be warned that, particularly if you do this last one, then every table
> throughout the database gets additional redo data logged. And, which is
> worse, if a table doesn't actually have a primary key (PeopleSoft,
> anyone?!!) then every column in the table is logged.
> Be afraid, however... very afraid. Used indiscriminately, the additional
> amount of redo this new feature causes to be created can swamp a
> finely-balanced redo subsystem. The performance implications can be very
> dramatic indeed.

I'm very afraid of this, but the only other option I have, is to use some other method, such as triggers (on one of the most heavily inserted tables in DB) or keep in transaction tables full history with moving data out using night batch to some other database via DB link or exp/imp. Neither of these two other solutions looks like great performance stimulant. I'm still thinking that suplemental redo infos, even if it means performance overhead, can serve better to these purposes. I really like the idea to move mining transactions from heavily loaded production machine to some other dedicated server.

P.S. Thanks not only to Howard, but to all of you who responded to my post.

Dusan Bolek Received on Mon Mar 31 2003 - 02:49:10 CST