Return
-
Title
When Replicating between Different Schemas Shareplex doesn't replicate create index correctly -
Description
with the following settings
SP_OCT_REPLICATE_ALL_DDL=1
SP_OCT_REPLICATE_DDL=3
SP_OCT_AUTOADD_ENABLE=1
The config file used the expand command to change the schemas between the source and target
expand a.% b.% target_system@o.SID
shareplex doesn't replicate the create index command, although it does properly replicate the create table and alter table commands.
poster stops due to error because it is trying to create the index as 'A' not 'B' this is due to the fact that create index is 9i ddl where the alter table is not.
-
Cause
Shareplex is replicating the create index command, but it is creating the object in the same schema on the target as the source even if the config files specifies a different schema. see below for an example off the create table and create index.
here is how shareplex currently replicates ddl source/target same instance.SQL> connect a/a
Connected.
SQL> show user
USER is "A"
SQL> desc a.foo
ERROR:
ORA-04043: object a.foo does not existSQL> desc b.foo
ERROR:
ORA-04043: object b.foo does not existSQL>
create table foo (a number);Table created.SQL> desc a.foo
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBERSQL> desc b.foo
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBERSQL>
alter table foo add constraint foo_pk primary key (a) ;SQL>
Table altered.SQL> SQL> desc a.foo
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER <- notice that the not null constraint is now on table so add primary key is now on table.SQL> desc b.foo
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER <- notice that the not null constraint is now on table so add primary key is now on table.
SQL> create table a.foo2 (cola number, colb number, colc number, cold number);Table created.SQL> desc a.foo
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBERSQL> desc b.foo< -
Resolution
Workaround:
1. Create the Index manually on the Target or Use the alter table command to add a primary key or unique key constraint to the table.
2. Restart Post it will skip the failed DDL and move forward.
Status: Fixed in future Release.
-
Defect ID
ST98560