Column Groups with DBMS_REPCAT

Column groups provide a mechanism for guaranteeing data consistency across one or more columns in a replicated table. Every replicated table has at least one column group, called the default column group, which Oracle creates automatically. You can also create your own column groups (and you probably should) in which you group logically related fields.

About Column Groups

Suppose that you have a customer table that contains address information, such as street address, city, state, and postal code, plus personal information such as last name, marital status, birth date, and phone number.

SQL> desc customer
 Name                   Null?           Type
 ---------------	--------	------
 CUSTOMER_ID                            NUMBER(6)
 NAME                                   VARCHAR2(30)
 MARITAL_STATUS                         VARCHAR2(1)
 PHONE_NUMBER                           VARCHAR2(16)
 STREET_ADDR                            VARCHAR2(30)
 CITY                                   VARCHAR2(30)
 STATE                                  VARCHAR2(30)
 POSTAL_CODE                            VARCHAR2(12)
 TIMESTAMP                              DATE
 GLOBAL_NAME                            VARCHAR2(30)

In this table, the fields pertaining to the customer’s address (i.e., STREET_ADDR, CITY, STATE, and POSTAL_CODE) are logically related. You would not want to allow an update at one site to set the CITY to “San Francisco” and an update at another site to set the STATE to “Mississippi” since (as of this writing) there is no such municipality as San Francisco, Mississippi.

Oracle’s answer to this potential catastrophe is the column group. A column group is a logical grouping of columns whose collective values are treated as a unit. If we create a column group and add the address-related fields ...

Get Oracle Built-in Packages now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.