接上上篇 ddl filter with built-in filter rules
双向复制ddl,dml，任意一节点创建测试用户，测试表（表要有主键），下面步骤两端都要执行
ggsci
add schematrandata 测试用户名称
ddl include all 无需调整
dml table map 添加测试用户映射
下面步骤两端都要执行：
配置需要登录GOLDENGATE管理用户
执行
sqlplus ogg/ogg
添加规则：（此规则测试未生效）
declare sno number;
begin
sno:=DDLAUX.ADDRULE(owner_name=>'TIGER', obj_type=> 2, command =>'CMD_DROP');
end;
/
添加规则：（此规则生效）
declare sno number;
begin
sno := DDLAUX.ADDRULE(owner_name => 'TIGER');
end;
/
其他规则维护操作例子：
goldengate 管理用户ogg下面的相关视图：
GGS_DDL_RULES
GGS_DDL_RULES_LOG
查询视图（规则）：
SQL> select sno from ggs_ddl_rules;
SNO
----------
1
2
3
删除规则：
declare result boolean;
begin
result := ddlaux.droprule(dsno=>1);
end;
/
declare result boolean;
begin
result := ddlaux.droprule(dsno=>2);
end;
/
declare result boolean;
begin
result := ddlaux.droprule(dsno=>3);
end;
/
附录
CREATE OR REPLACE PACKAGE DDLAux AS
TB_IOT CONSTANT NUMBER := 960;
TB_CLUSTER CONSTANT NUMBER := 1024;
TB_NESTED CONSTANT NUMBER := 8192;
TB_TEMP CONSTANT NUMBER := 12582912;
TB_EXTERNAL CONSTANT NUMBER := 2147483648;
TYPE_INDEX CONSTANT NUMBER := 1;
TYPE_TABLE CONSTANT NUMBER := 2; ####################################################
TYPE_VIEW CONSTANT NUMBER := 4;
TYPE_SYNONYM CONSTANT NUMBER := 5;
TYPE_SEQUENCE CONSTANT NUMBER := 6;
TYPE_PROCEDURE CONSTANT NUMBER := 7;
TYPE_FUNCTION CONSTANT NUMBER := 8;
TYPE_PACKAGE CONSTANT NUMBER := 9;
TYPE_TRIGGER CONSTANT NUMBER := 12;
CMD_CREATE CONSTANT varchar2(10) := 'CREATE';
CMD_DROP CONSTANT varchar2(10) := 'DROP';
CMD_TRUNCATE CONSTANT varchar2(10) := 'TRUNCATE';
CMD_ALTER CONSTANT varchar2(10) := 'ALTER';
/* Add a rule for inclusion or exclusion so that DDL trigger will handle
* the matching object appropriately. Rules are evaluated in the sorted
* order (asc) of sno. If the sno is not specified then the rule will be
* added in the tail end (max(sno) + 1). If the user
* want to position the rule inbetween two already existing rule
* could use decimals in between.
* The users can place rules as 11.1, 11.2 etc.
* The rules added will be placed in the table GGS_DDL_RULES
* Rule addition examples
* To exclude all objects having name likeGGS%
* addRule(obj_name=> 'GGS%');
* To exclude all temporary table
* addRule(base_obj_property => TB_TEMP, obj_type => TYPE_TABLE);
* To exclude all External table
* addRule(base_obj_property => TB_EXTERNAL, obj_type => TYPE_TABLE);
* To exclude all INDEXES on External table
* addRule(base_obj_property => TB_EXTERNAL, obj_type => TYPE_INDEX);
* To exclude all truncate table ddl
* addRule(obj_type=>TYPE_TABLE, command => CMD_TRUNCATE);
*
*/
FUNCTION addRule(obj_name IN VARCHAR2 DEFAULT NULL,
base_obj_name IN VARCHAR2 DEFAULT NULL,
owner_name IN VARCHAR2 DEFAULT NULL,
base_owner_name IN VARCHAR2 DEFAULT NULL,
base_obj_property IN NUMBER DEFAULT NULL,
obj_type IN NUMBERDEFAULT NULL,
command IN VARCHAR2 DEFAULT NULL,
inclusion IN boolean DEFAULT NULL ,
sno IN NUMBER DEFAULT NULL)
RETURN NUMBER;
/* Drop rule by the rule serial number */
FUNCTION dropRule(dsno IN NUMBER) RETURN BOOLEAN;
PROCEDURE listRules;
/* This function returns TRUE if the current ddl object should be skipped
* FALSE if it should not be skipped.
* This function consults the GGS_DDL_RULES table to check for inclusion
* or exclusion. All excluded objects are logged into the table
* GGS_DDL_RULES_LOG
*/
FUNCTION SKIP_OBJECT(obj_id IN NUMBER, base_obj_id IN OUT NUMBER,
OBJ_NAME varchar2, obj_owner varchar2,
obj_type NUMBER, base_obj_name varchar2,
base_owner_name varchar2,
command varchar2)
RETURN BOOLEAN ;
/* Records an exclusion in GGS_DDL_RULES_LOG table */
PROCEDURE recordExclusion(sno IN NUMBER, OBJ_NAME varchar2,
obj_owner varchar2,
obj_type NUMBER, base_obj_name varchar2,
base_owner_name varchar2, base_obj_property number,
command varchar2);
CURSOR ignoreObj IS
SELECT sno, obj_name, owner_name, base_obj_name, base_owner_name,
base_obj_property, obj_type, command,inclusion
from "OGG"."GGS_DDL_RULES" order by sno;
END DDLAux;
页:
[1]