What is the parameter SP_OCT_REPLICATE_CTAS
General information
The parameter is applicable in situations where the “CREATE TABLE AS SELECT” is executed on the table on source which qualifies for replication by virtue of the wildcard entry and it is desired to replicate the table creation and the underlying data to the target. For example, on source the following SQL statement is executed:
SQL>CREATE TABLE TABLE1 AS SELECT * FROM TABLE2;
(In the following para the TABLE1 is termed as derived table and the TABLE2 is termed as base table)
The Capture parameter SP_OCT_REPLICATE_CTAS needs to be set to 1 or 0 on source where 1 is the default. The change in its value requires stop/start of the Capture process for the change to take effect.
If the parameter is set to 1, it only replicates the DDL for the derived table and the derived table is populated on target (from the base table) after the base table is first created on target by the replicating DDL. If the base table is not sync’d up across source and target, then the derived table will not be in sync. The advantage is, the DML associated is not replicated to the target but rather populated on target by using the target base table as a reference thereby reducing the data sent across from source to target. The base table need not be in replication, but if so, the derived table on target may not remain in sync with that on source so it may need to be sync’d up with reference to the source table using repair or other means.
If the parameter is set to 0, then the underlying DDL as well as the DML on the table is replicated across from source which is then created on target due to the DDL and is also populated on the target from the DML replicated across the queue. The source database needs to be in archivelog mode for the DML to replicate across as the DML generated on source are a result of DLOAD. In this approach, the advantage is that there is no scope for out of sync as the the DML associated with populating the derived table from base table occurs on source and the base table on source is always the trusted table in terms of the data integrity.
