New Oracle Constraint Type

Question

I have to admit that this just caught me by surprise in a production system. I recently added supplemental logging to a few tables for use with Oracle Change Data Capture. A routine check during an unrelated code build revealed that this apparently created disabled constraints in the database of type "S". I can't seem to find any reference to this in the Oracle docs and the single "S" makes it hard to Google for something relevant.

My questions:

1. Can anyone thing of a reason that supplemental logging results in an implicit constraint?
2. Why is it created with a DISABLED state?
3. Does anyone have experience with the effects of enabling these? We have a standard clean-up process that runs after deployments to fully enable constraints that may have been disabled or enabled novalidate for data migration reasons.

Answer 1

Constraint type is documented: http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/statviews_1045.htm#REFRN20047

S stands for "Supplemental logging", which is further explained here: http://download.oracle.com/docs/cd/E11882_01/server.112/e10704/strms_glossary.htm#CHDIHHGA and here: http://download.oracle.com/docs/cd/E11882_01/server.112/e10705/prep_rep.htm#STREP107

I don't have any experience with it. This is the result of searching on tahiti.oracle.com.

Regards, Rob.

Answer 2

Supplemental logging is necessary to support certain types of asynchronous data capture: AQ Streams for instance, and CDC (both Oracle's and third party's implementations). These mechanisms work by reading the redo logs and applying those changes to some other Oracle database. The point of supplemental logging is to increase the amount of data included in the redo logs, .

There are two ways of enabling supplemental logging. In more recent versions of the database we can set minimal logging at the database level

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

In additional we can add specify tables and columns in Supplemental Log Groups. The point of this is to include the values for unchanged columns in the change table, as it makes it easier to apply the changes in the target database. Find out more.

Obviously the S type constraint identifies columns in a Supplemental Log Group. I think the reason they are disabled is because they do not enforce a data integrity rule (unlike primary keys or check constraints). If so, I think it would be unwise to enable them, and so you should re-write your automatic clean-up to filter constraints of type S.