After upgrading source database to 11gR2, GoldenGate extract
causing DDL errors which are generated in below mentioned form:
2013-02-17 11:55:15
ERROR OGG-00516 Oracle GoldenGate Delivery for Oracle,
r01_salt.prm: Fatal error executing DDL
replication: error [Error code [922], ORA-00922: missing or invalid option, SQL
create table "XXXXXX"."DBMS_TABCOMP_TEMP_CMP" organization
heap tablespace "XXXXX"
compress for all operations nologging as select /*+ DYNAMIC_SAMPLING(0) */ *
from "XXXXXX".DBMS_TABCOMP_TEMP], due to explicit ABEND error
handling and filter [include all (default)].
Tables referenced which cannot be
created are:
DBMS_TABCOMP_TEMP_CMP
DBMS_TABCOMP_TEMP_UNCMP
Cause:
Compression advisor is a new
advisor added in 11gR2. It is shipped with Segment Advisor, and will be
executed whenever Segment Advisor task run. It can also be invoked manually.
Segments that are at least 10MB
in size and with at least 3 indexes will be evaluated for compressibility (but
not all the tables which meet the requirements will be evaluated, an internal
algorithm will decide which segment should be evaluated).
dbms_compression package will be invoked to estimate how much space can be
saved. It will create temporary internal table either DBMS_TABCOMP_TEMP_UNCMP
or DBMS_TABCOMP_TEMP_CMP) in the tablespace which hosts the segment. These two
tables will be dropped immediately after the estimation.
If DDL replication is configured,
Extract will capture DDL for these temporary tables which will be as follows:
create table "XXXXXX"."DBMS_TABCOMP_TEMP_UNCMP"
tablespace "XXXXX" nologging as select /*+ DYNAMIC_SAMPLING(0) FULL("XXXXXX"."BATCH")
*/ * from "XXXXX"."BATCH"
Replicat is having following
mapping parameter:
MAP source_schema.*, TARGET dest_schema.*;
This ability to remap schemas in
a Create Table As Select (CTAS) SQL statement is raised as an enhancement
request in Bug:9106992.
Note: in the case above , nologging is specified so it would not be relevant or
appropriate to capture changes in this case.
Workaround:
Exclude tables :
DDL ..., EXCLUDE OBJNAME <schema>.DBMS_TABCOMP_TEMP_UNCMP, &EXCLUDE OBJNAME <schema>.DBMS_TABCOMP_TEMP_CMP
Reference:
DDL / Remap Schema : ORA-942 Connected With DBMS_TABCOMP_TEMP_CMP &
DBMS_TABCOMP_TEMP_UNCMP [ID 1505178.1]
