CREATE TABLE AS SELECT
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;