How to use SHAREPLEX_DDL_CONTROL to exclude certain DDL operations on NON-REPLICATION objects.
RESOLUTION
New feature since version 7.6.
SHAREPLEX_DDL_CONTROL table has DDL_CODE column which can be used to exclude certain ddl operations.
On source SP_OCT_REPLICATE_ALL_DDL should be set to 1 to replicate ddls for objects not in replication.
A wild card or null can be used to exclude the ddl operation on all schema and all objects.
Use the decimal value to populate the DDL_CODE column.
Procedure:
For example:
insert into <shareplex_owner>.SHAREPLEX_DDL_CONTROL(DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (9, '%', '%' );
insert into <shareplex_owner>.SHAREPLEX_DDL_CONTROL(DDL_CODE, SCHEMA_FILTER, OBJECT_FILTER) values (11, 'TEST_%', '%' );
Starting in 8.6.2, SharePlex allows DDL filtering on a particular DDL on a range of objects based on schema or object combination.
For example, if you want to exclude only create table for tony.TEST123.
SQL> insert into shareplex_ddl_control (ddl_code, schema_filter, object_filter) values (1,'TONY','TEST123');
1 row created.
SQL> commit;
ocap_ddl 2016-03-26 07:32:09.774590 22446 4209776064 User Name: "TONY" Status: Filtered
ocap_ddl 2016-03-26 07:32:09.774590 22446 4209776064 DDL = create table TEST123 (id number)
ocap_ddl 2016-03-26 07:32:09.774590 22446 4209776064 Oracle Time: 03/26/16 07:32:05 object=TONY.TEST123
ocap_ddl 2016-03-26 07:32:31.809128 22446 4209776064 User Name: "TONY" Status: Replicated
ocap_ddl 2016-03-26 07:32:31.809128 22446 4209776064 DDL = alter table TEST123 add primary key(id)
ocap_ddl 2016-03-26 07:32:31.809128 22446 4209776064 Oracle Time: 03/26/16 07:32:29 object=TONY.TEST123
ocap_ddl 2016-03-26 07:34:18.950209 22446 4209776064 User Name: "TONY" Status: Replicated
ocap_ddl 2016-03-26 07:34:18.950209 22446 4209776064 DDL = create table test12345 (id number)
ocap_ddl 2016-03-26 07:34:18.950209 22446 4209776064 Oracle Time: 03/26/16 07:34:16 object=TONY.TEST12345
ocap_ddl 2016-03-26 07:34:39.972560 22446 4209776064 User Name: "TONY" Status: Replicated
ocap_ddl 2016-03-26 07:34:39.972560 22446 4209776064 DDL = create index test12345_idx on test12345(id)
ocap_ddl 2016-03-26 07:34:39.972560 22446 4209776064 Oracle Time: 03/26/16 07:34:37 object=TONY.TEST12345_IDX
Another example, if you want to exclude alter index for TONY schema.
SQL> insert into shareplex_ddl_control (ddl_code, schema_filter, object_filter) values (11,'TONY',NULL);
1 row created.
Commit complete.
ocap_ddl 2016-03-26 07:49:57.579567 22446 4209776064 User Name: "TONY" Status: Replicated
ocap_ddl 2016-03-26 07:49:57.579567 22446 4209776064 DDL = alter table test123 add (name varchar2(10))
ocap_ddl 2016-03-26 07:49:57.579567 22446 4209776064 Oracle Time: 03/26/16 07:49:55 object=TONY.TEST123
ocap_ddl 2016-03-26 07:50:08.597282 22446 4209776064 User Name: "TONY" Status: Replicated
ocap_ddl 2016-03-26 07:50:08.597282 22446 4209776064 DDL = create index test123_idx on test123(name)
ocap_ddl 2016-03-26 07:50:08.597282 22446 4209776064 Oracle Time: 03/26/16 07:50:06 object=TONY.TEST123_IDX
ocap_ddl 2016-03-26 07:51:43.710705 22446 4209776064 User Name: "TONY" Status: Filtered
ocap_ddl 2016-03-26 07:51:43.710705 22446 4209776064 DDL = alter index test123_idx rebuild online
ocap_ddl 2016-03-26 07:51:43.710705 22446 4209776064 Oracle Time: 03/26/16 07:51:41 object=TONY.TEST123_IDX
DDL code in decimal
DDL code in decimal | |
CREATE_TABLE | 1 |
CREATE_CLUSTER | 4 |
DROP_CLUSTER | 8 |
CREATE_INDEX | 9 |
DROP_INDEX | 10 |
ALTER_INDEX | 11 |
DROP_TABLE | 12 |
CREATE_SEQUENCE | 13 |
ALTER_SEQUENCE | 14 |
ALTER_TABLE | 15 |
DROP_SEQUENCE | 16 |
GRANT | 17 |
REVOKE | 18 |
CREATE_SYNONYM | 19 |
DROP_SYNONYM | 20 |
CREATE_VIEW | 21 |
DROP_VIEW | 22 |
CREATE_PROCEDURE | 24 |
ALTER_PROCEDURE | 25 |
RENAME | 28 |
COMMENT_TABLE_COL | 29 |
CREATE_DB_LINK | 32 |
DROP_DB_LINK | 33 |
ALTER_DATABASE | 35 |
ALTER_USER | 43 |
CREATE_USER | 51 |
CREATE_ROLE | 52 |
DROP_USER | 53 |
DROP_ROLE | 54 |
CREATE_SCHEMA | 56 |
CREATE_TRIGGER | 59 |
ALTER_TRIGGER | 60 |
DROP_TRIGGER | 61 |
ANALYZE_TABLE | 62 |
ANALYZE_INDEX | 63 |
ANALYZE_CLUSTER | 64 |
DROP_PROCEDURE | 68 |
CREATE_TYPE | 77 |
DROP_TYPE | 78 |
ALTER_ROLE | 79 |
ALTER_TYPE | 80 |
CREATE_TYPE_BODY | 81 |
DROP_TYPE_BODY | 83 |
TRUNCATE | 85 |
ALTER_VIEW | 88 |
CREATE_FUNCTION | 91 |
ALTER_FUNCTION | 92 |
DROP_FUNCTION | 93 |
CREATE_PACKAGE | 94 |
ALTER_PACKAGE | 95 |
DROP_PACKAGE | 96 |
CREATE_PACKAGE_BODY | 97 |
ALTER_PACKAGE_BODY | 98 |
DROP_PACKAGE_BODY | 99 |
CREATE_DIRECTORY | 157 |
DROP_DIRECTORY | 158 |
ASSOCIATE_STATISTICS | 168 |
DISASSOCIATE_STATISTICS | 169 |
Please use only decimal when you specify the DDL Filter value.
© ALL RIGHTS RESERVED. 使用条款 隐私 Cookie Preference Center