Prerequisites for Configuring DDL
The CREATE TABLE AS SELECT (CTAS) statements include
SELECT statements and INSERT statements that
reference any number of underlying objects. By default, Oracle GoldenGate obtains
the data for the AS SELECT clause from the target database. You can
force the CTAS operation to preserve the original inserts using this parameter.
Note:
For this reason, Oracle XMLType tables created from
a CTAS (CREATE TABLE AS SELECT) statement
cannot be supported. For XMLType tables, the row object IDs
must match between source and target, which cannot be maintained in this
scenario. XMLType tables created by an empty
CTAS statement (that does not insert data in the new table)
can be maintained correctly.
In addition, you could use the GETCTASDML parameter
that allows CTAS to replay the inserts of the CTAS thus preserving OIDs during
replication. This parameter is only supported with Integrated Dictionary and any
downstream Replicat must be 12.1.2.1 or greater to consume the trail otherwise,
there may be divergence.
The objects in the AS SELECT clause must exist in the
target database, and their names must be identical to the ones on the source.
In a MAP statement, Oracle GoldenGate only maps the name
of the new table (CREATE TABLE name) to the TARGET
specification, but does not map the names of the underlying objects from the
AS
SELECT clause. There could be dependencies on those objects that
could cause data inconsistencies if the names were converted to the
TARGET specification.
The following shows an example of a CREATE TABLE AS
SELECT statement on the source and how it would be replicated to the
target by Oracle GoldenGate.
CREATE TABLE a.tab1 AS SELECT * FROM a.tab2;
The MAP statement for Replicat is as follows:
MAP a.tab*, TARGET a.x*;
The target DDL statement that is applied by Replicat is the following:
CREATE TABLE a.xtab1 AS SELECT * FROM a.tab2;
The name of the table in the AS SELECT * FROM clause
remains as it was on the source: tab2 (rather than
xtab2).
To keep the data in the underlying objects consistent on source and target, you can configure them for data replication by Oracle GoldenGate. In the preceding example, you could use the following statements to accommodate this requirement:
Source
TABLE a.tab*;Target
MAPEXCLUDE a.tab2
MAP a.tab*, TARGET a.x*;
MAP a.tab2, TARGET a.tab2;