Significance of SUPPLEMANTAL LOG FILE (PRIMARY KEY) in Oracle v9i!

[amitstora]

Hi all!

Query:

1.What is the significance of the command in Oracle v9i:

ALTER TABLE ABC ADD SUPPLEMENTAL LOG FILE(PRIMARY KEY)
COLUMNS;

Thanks & Regards,

Amit.

[Sameer]

From documentation...

9-42 Oracle9 i SQL Reference

ADD SUPPLEMENTAL LOG DATA Clause:

Specify the ADD SUPPLEMENTAL LOG DATA clause to place additional column data into the log stream any time an update operation is performed. These four keywords alone enable minimal supplemental logging, which is not enabled by default. Minimal supplemental logging ensures that Logminer (and any products building on Logminer technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables. If supplemental log data will be the source of change in another database, such as a logical standby, the log data must also uniquely identify each row updated. In this case, you should enable identification key ("full") supplemental logging by specifying PRIMARY KEY COLUMNS and UNIQUE KEY COLUMNS.

PRIMARY KEY COLUMNS When you specify PRIMARY KEY COLUMNS, Oracle ensures, for all tables with a primary key, that all columns of the primary key are placed into the redo log whenever an update operation is performed. If no primary key is defined, Oracle places into the redo log a set of columns that uniquely identifies the row. This set may include all columns with a fixed-length maximum size. UNIQUE INDEX COLUMNS When you specify UNIQUE INDEX COLUMNS, Oracle ensures, for all tables with a unique key, that if any unique key columns are ALTER DATABASE modified, all other columns belonging to the unique key are also placed into the redo log. DROP SUPPLEMENTAL LOG DATA Clause Use the DROP SUPPLEMENTAL LOG DATA clause to instruct Oracle to stop placing additional log information into the redo log stream whenever an update operation occurs. This statement terminates the effect of a previous ADD SUPPLEMENTAL LOG
DATA statement.

Note: You can issue this statement when the database is open.

However, Oracle will invalidate all DML cursors in the cursor cache, which will have an effect on performance until the cache is repopulated. See Also: Oracle9i Data Guard Concepts and Administration for information on supplemental logging

+

http://www.bikle.com/TThier/DBAtips/...3/logMiner.htm

HTH

Sameer

[amitstora]

Hi Sameer!

Many thanks for the info.I am really benefitted with your reply.

Thanks once again for taking out time.

Regards,

Amit.