How do the ALTER TABLE to add column differ in SharePlex 9.1 and up versus earlier versions when configuring vertical partitioning with exclusion clause?
There is one major difference in the way the vertical partitioning treats the newly added column for tables with exclusion column partition in vertical partitioning where the vertical partitioned replication existed prior to the adding of new column:
For versions 9.1 and up:
The newly added column will be brought into replication. For example, if I initially had the following columns in my table named table2:
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_COL NUMBER
CHAR_COL1 VARCHAR2(10)
CHAR_COL2 VARCHAR2(10)
The following is my active config file:
sp_ctrl (alvsupl14:8653)> view config verticaltest3
datasource:o.ORA11GR2
sha91.table2 !(CHAR_COL2) sha91.table2 alvsupl18@o.ora11gr2
If I now add another column named CHAR_COL3 to my source table as:
SQL> alter table table2 add (char_col3 varchar2(10));
The DML to this column named CHAR_COL3 will be replicated. The table named TABLE2 will look like this:
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_COL NUMBER
CHAR_COL1 VARCHAR2(10)
CHAR_COL2 VARCHAR2(10)
CHAR_COL3 VARCHAR2(10)
Where the DML to column CHAR_COL2 is not replicated due to the exclusion clause in vertical partitioning.
The dump of the object cache will also corroborate this fact. Here is the dump named source.out:
cat source.out
"SHA91"."TABLE2"
998281
Type: TABLE Vertically partitioned
Key Type: No unique key Number of key columns: ..
Number of columns: 4
Status: In Replication
--------------------------------------------------------------------------------
Col |Length|Type |Name |Column info
1 22 NUMBER NUM_COL Key, Nullable
2 10 VARCHAR2 CHAR_COL1 Key, Nullable
3 10 VARCHAR2 CHAR_COL2 Column not replicated
4 10 VARCHAR2 CHAR_COL3 Key, Nullable
For versions below 9.1:
There is a marked difference in the behavior of exclusion clause in versions prior to SharePlex 9.1.
I initially had the following columns in my table named table2:
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_COL NUMBER
CHAR_COL1 VARCHAR2(10)
CHAR_COL2 VARCHAR2(10)
The following is my active config file:
sp_ctrl (alvsupl14:8653)> view config verticaltest3
datasource:o.ORA11GR2
sha91.table2 !(CHAR_COL2) sha91.table2 alvsupl18@o.ora11gr2
If I now add another column named CHAR_COL3 to my source table as:
SQL> alter table table2 add (char_col3 varchar2(10));
The DML to this column named CHAR_COL3 will not be replicated. The table named TABLE2 will look like this:
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM_COL NUMBER
CHAR_COL1 VARCHAR2(10)
CHAR_COL2 VARCHAR2(10)
CHAR_COL3 VARCHAR2(10)
Where the DML to column CHAR_COL2 and CHAR_COL3 is not replicated due to the exclusion clause in vertical partitioning. In essence, the exclusion clause initially excludes all columns forming part of the clause. However, all columns subsequently added to the source table are construed as being part of the exclusion clause and are not replicated. The dump of the object cache confirms this fact:
cat source.out
"SHA91"."TABLE2"
998281
Type: TABLE Vertically partitioned
Key Type: No unique key Number of key columns: ..
Number of columns: 4
Status: In Replication
--------------------------------------------------------------------------------
Col |Length|Type |Name |Column info
1 22 NUMBER NUM_COL Key, Nullable
2 10 VARCHAR2 CHAR_COL1 Key, Nullable
3 10 VARCHAR2 CHAR_COL2 Column not replicated
4 10 VARCHAR2 CHAR_COL3 Column not replicated
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center