oracle sync capture脚本生成程序

最新推荐文章于 2022-07-10 23:21:52 发布

skillfulit

最新推荐文章于 2022-07-10 23:21:52 发布

阅读量235

点赞数

CC 4.0 BY-SA版权

分类专栏： oracle

本文链接：https://blog.youkuaiyun.com/skillfulit/article/details/88691912

oracle 专栏收录该内容

11 篇文章

订阅专栏

该博客展示了一个Oracle存储过程P_GEN_ORA_STRMS_SYNC_CAPTURE，用于实现流同步捕获。包含多个子过程，如P_QUEUE、P_APPLY等，分别处理队列设置、应用创建、传播规则添加、实例化和启动应用等操作，通过循环对不同模式和后缀进行处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE OR REPLACE PROCEDURE P_GEN_ORA_STRMS_SYNC_CAPTURE IS
V_STEP PLS_INTEGER := 1;
V_CONN_STRM_SRC CONSTANT VARCHAR2(200) := 'connect strmadmin/1@srcdb.dpc' || CHR(10);
V_CONN_STRM_DST CONSTANT VARCHAR2(200) := 'connect strmadmin/1@dstdb.dpc' || CHR(10);
PROCEDURE P_QUEUE
(
P_SCHM VARCHAR2,
P_SUFFIX VARCHAR2
) IS

V_CQUEUE CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
QUEUE_TABLE => 'capqueue_t:suffix',
QUEUE_NAME => 'capqueue_:suffix');
END;
/

}';

V_AQUEUE CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
QUEUE_TABLE => 'appqueue_t:suffix',
QUEUE_NAME => 'appqueue_:suffix');
END;
/

}';
V_SCRPT VARCHAR2(1000);
BEGIN

/*src*/
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_SRC);

V_SCRPT := REPLACE(V_CQUEUE, ':suffix', P_SUFFIX);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 1, V_SCRPT);

V_SCRPT := REPLACE(V_AQUEUE, ':suffix', P_SUFFIX);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 2, V_SCRPT);

/*

dst
--------------------------
*/
V_STEP := V_STEP + 1;

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_DST);

V_SCRPT := REPLACE(V_CQUEUE, ':suffix', P_SUFFIX);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 1, V_SCRPT);

V_SCRPT := REPLACE(V_AQUEUE, ':suffix', P_SUFFIX);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 2, V_SCRPT);

COMMIT;
END;

PROCEDURE P_APPLY
(
P_SCHM VARCHAR2,
P_SUFFIX VARCHAR2
) IS

V_APP_PROC CONSTANT CLOB := q'{
BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => 'strmadmin.appqueue_:suffix',
apply_name => 'app_proc_:suffix',
apply_captured => FALSE);
END;
/

}';
V_APP_RULE_STOD CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ':table_name',
streams_type => 'apply',
streams_name => 'app_proc_:suffix',
queue_name => 'strmadmin.appqueue_:suffix',
source_database => 'dstdb.dpc');
END;
/

}';

V_APP_RULE_DTOS CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ':table_name',
streams_type => 'apply',
streams_name => 'app_proc_:suffix',
queue_name => 'strmadmin.appqueue_:suffix',
source_database => 'srcdb.dpc');
END;
/

}';

V_TMP VARCHAR2(2000);
V_SCRPT VARCHAR2(2000);
BEGIN
NULL;

/*src*/
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_SRC);

V_SCRPT := REPLACE(V_APP_PROC, ':suffix', P_SUFFIX);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 1, V_SCRPT);

V_TMP := REPLACE(V_APP_RULE_STOD, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;

/*dst
---------------------
*/
V_STEP := V_STEP + 1;

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_DST);
V_SCRPT := REPLACE(V_APP_PROC, ':suffix', P_SUFFIX);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 1, V_SCRPT);

V_TMP := REPLACE(V_APP_RULE_DTOS, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;

COMMIT;
END;

PROCEDURE P_PROPAGATION
(
P_SCHM VARCHAR2,
P_SUFFIX VARCHAR2
) IS
V_SEND_STOD CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ':table_name',
streams_name => 'send_proc_:suffix',
source_queue_name => 'strmadmin.capqueue_:suffix',
destination_queue_name => 'strmadmin.appqueue_:suffix@dstdb.dpc',
source_database => 'srcdb.dpc',
queue_to_queue => TRUE);
END;
/

}';

V_SEND_DTOS CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
table_name => ':table_name',
streams_name => 'send_proc_:suffix',
source_queue_name => 'strmadmin.capqueue_:suffix',
destination_queue_name => 'strmadmin.appqueue_:suffix@srcdb.dpc',
source_database => 'dstdb.dpc',
queue_to_queue => TRUE);
END;
/

}';
V_TMP VARCHAR2(2000);
V_SCRPT VARCHAR2(2000);
BEGIN
NULL;
/*src*/
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_SRC);

V_TMP := REPLACE(V_SEND_STOD, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;

/*dst*/
V_STEP := V_STEP + 1;

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_DST);

V_TMP := REPLACE(V_SEND_DTOS, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;
COMMIT;
END;

PROCEDURE P_SYNCHRONOUS
(
P_SCHM VARCHAR2,
P_SUFFIX VARCHAR2
) IS
V_SYNC_STOD CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ':table_name',
streams_type => 'sync_capture',
streams_name => 'sync_capture_:suffix',
queue_name => 'strmadmin.capqueue_:suffix');
END;
/

}';

V_SYNC_DTOS CONSTANT CLOB := q'{
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => ':table_name',
streams_type => 'sync_capture',
streams_name => 'sync_capture_:suffix',
queue_name => 'strmadmin.capqueue_:suffix');
END;
/

}';
V_TMP VARCHAR2(2000);
V_SCRPT VARCHAR2(2000);
BEGIN
NULL;
/*src*/
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_SRC);

V_TMP := REPLACE(V_SYNC_STOD, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;

/*dst*/
V_STEP := V_STEP + 1;

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_DST);

V_TMP := REPLACE(V_SYNC_DTOS, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;

COMMIT;
END;

PROCEDURE P_INSTANTIATION
(
P_SCHM VARCHAR2,
P_SUFFIX VARCHAR2
) IS
V_INST_SCN_STOD CONSTANT CLOB := q'{
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@dstdb.dpc(
source_object_name => ':table_name',
source_database_name => 'srcdb.dpc',
instantiation_scn => iscn);
END;
/
}';

V_INST_SCN_DTOS CONSTANT CLOB := q'{
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@srcdb.dpc(
source_object_name => ':table_name',
source_database_name => 'dstdb.dpc',
instantiation_scn => iscn);
END;
/

}';
V_TMP VARCHAR2(2000);
V_SCRPT VARCHAR2(2000);
BEGIN
NULL;
/*src*/
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_SRC);

V_TMP := REPLACE(V_INST_SCN_STOD, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;

/*dst*/
V_STEP := V_STEP + 1;

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_DST);

V_TMP := REPLACE(V_INST_SCN_DTOS, ':suffix', P_SUFFIX);
FOR T IN (SELECT P_SCHM || '.' || TBN TBN, V_STEP * 100 + SNO SNO FROM STRM_SYNC_TBLS ORDER BY SNO)
LOOP
NULL;
V_SCRPT := REPLACE(V_TMP, ':table_name', T.TBN);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, T.SNO, V_SCRPT);
END LOOP;
END;

PROCEDURE P_START_APPLY
(
P_SCHM VARCHAR2,
P_SUFFIX VARCHAR2
) IS
V_START_APPLY CONSTANT CLOB := q'{
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'app_proc_:suffix');
END;
/
}';
V_SCRPT VARCHAR2(2000);
BEGIN
V_SCRPT := REPLACE(V_START_APPLY, ':suffix', P_SUFFIX);
/*src*/
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_SRC);

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 1, V_SCRPT);

/*dst*/
V_STEP := V_STEP + 1;

INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 0, V_CONN_STRM_DST);
INSERT INTO STRM_SYNC_SCPT (SCHM, STP, NO_IN_STP, SCRPT) VALUES (P_SCHM, V_STEP, 1, V_SCRPT);

COMMIT;
END;

BEGIN
DELETE FROM STRM_SYNC_SCPT;
COMMIT;

FOR R IN (SELECT * FROM STRM_SYNC_SHMS)
LOOP
V_STEP := 2;
P_QUEUE(R.SCHM, R.SUFFIX);

V_STEP := V_STEP + 1;
P_APPLY(R.SCHM, R.SUFFIX);

V_STEP := V_STEP + 1;
P_PROPAGATION(R.SCHM, R.SUFFIX);

V_STEP := V_STEP + 1;
P_SYNCHRONOUS(R.SCHM, R.SUFFIX);

V_STEP := V_STEP + 1;
P_INSTANTIATION(R.SCHM, R.SUFFIX);

V_STEP := V_STEP + 1;
P_START_APPLY(R.SCHM, R.SUFFIX);

END LOOP;
END P_GEN_ORA_STRMS_SYNC_CAPTURE;