列名 | 型 | 制約等 |
ABBR | VARCHAR2(4) | PK |
NAME | VARCHAR2(25) | |
LAND_KM | NUMBER(12) |
列名 | 型 | 制約等 |
WINDOW_ID | NUMBER | |
ABBR | VARCHAR2(4) |
列名 | 型 | 制約等 |
JRN_FLAG | VARCHAR2(1) | 実表データが削除済みであれば'D'、それ以外は'I'が入る |
JRN_DATE | DATE | SYSDATE |
JRN_SUBSCRIBER | VARCHAR2(400 CHAR) | サブスクライバ名 |
ABBR | VARCHAR2(4) | ジャーナル対象テーブルの更新後の値 |
NAME | VARCHAR2(25) | |
LAND_KM | NUMBER(12) |
CREATE OR REPLACE FORCE EDITIONABLE VIEW "HR"."JV$CON_PART" ("JRN_FLAG", "JRN_DATE", "JRN_SUBSCRIBER", "ABBR", "NAME", "LAND_KM") AS select decode(TARG.ROWID, null, 'D', 'I') AS JRN_FLAG, sysdate AS JRN_DATE, JRN.CDC_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.ABBR AS ABBR ,TARG.NAME AS NAME, TARG.LAND_KM AS LAND_KM from ( select J.ABBR AS ABBR, SUB.CDC_SUBSCRIBER AS CDC_SUBSCRIBER from HR.J$CON_PART J, HR.SNP_CDC_SUBS SUB where SUB.CDC_SET_NAME = 'HR.CONCON' and J.WINDOW_ID > SUB.MIN_WINDOW_ID and J.WINDOW_ID <= SUB.MAX_WINDOW_ID group by J.ABBR, SUB.CDC_SUBSCRIBER ) JRN, HR.CON_PART TARG where JRN.ABBR = TARG.ABBR (+);
列名 | 型 | 制約等 |
JRN_FLAG | VARCHAR2(1) | 実表データが削除済みであれば'D'、それ以外は'I'が入る |
JRN_DATE | DATE | SYSDATE |
JRN_SUBSCRIBER | VARCHAR2(400 CHAR) | SNP_CDC_SUBS表のサブスクライバ名 |
ABBR | VARCHAR2(4) | ジャーナル対象テーブルの更新後の値 |
NAME | VARCHAR2(25) | |
LAND_KM | NUMBER(12) |
CREATE OR REPLACE FORCE EDITIONABLE VIEW "HR"."JV$DCON_PART" ("JRN_FLAG", "JRN_DATE", "JRN_SUBSCRIBER", "ABBR", "NAME", "LAND_KM") AS select decode(TARG.ROWID, null, 'D', 'I') AS JRN_FLAG, sysdate AS JRN_DATE, JRN.CDC_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.ABBR AS ABBR ,TARG.NAME AS NAME, TARG.LAND_KM AS LAND_KM from ( select J.ABBR AS ABBR, SUB.CDC_SUBSCRIBER AS CDC_SUBSCRIBER from HR.J$CON_PART J, HR.SNP_CDC_SUBS SUB, ALL_APPLY_PROGRESS APP where SUB.CDC_SET_NAME = 'HR.CONCON' and J.WINDOW_ID > SUB.MIN_WINDOW_ID and J.WINDOW_ID <= APP.APPLIED_MESSAGE_NUMBER group by J.ABBR, SUB.CDC_SUBSCRIBER ) JRN, HR.CON_PART TARG where JRN.ABBR = TARG.ABBR (+);
ALL_APPLY_PROGRESSビュー ↑ created by DBA_APPLY_PROGRESSビュー ↑ created by _DBA_APPLY_MILESTONEビュー ↑ created by sys.streams$_apply_milestone表
PROCEDURE ODI_UPDATE_HANDLER (in_any IN ANYDATA) IS lcr SYS.LCR$_ROW_RECORD; rc PLS_INTEGER; key_chg BOOLEAN; BEGIN rc := in_any.GETOBJECT(lcr); key_chg := FALSE; IF (lcr.GET_VALUE('NEW', 'ABBR', 'N') IS NOT NULL) THEN -- ★ 主キーが更新されていれば、TRUE key_chg := TRUE; END IF; INSERT INTO HR.J$CON_PART -- ★ 実表レコードの主キー(old)と、コミット時のSCNをWINDOW_IDとしてINSERT (ABBR, WINDOW_ID) VALUES (lcr.GET_VALUE('OLD', 'ABBR').AccessVarchar2(), lcr.get_commit_scn); IF (key_chg) THEN INSERT INTO HR.J$CON_PART -- ★ 主キーが更新されていれば、新しい値とSCNをINSERT (ABBR, WINDOW_ID) VALUES (lcr.GET_VALUE('NEW', 'ABBR', 'Y').AccessVarchar2(), lcr.get_commit_scn); END IF; END;
実行ユーザ | COMMIT_SCN | START_TIMESTAMP | COMMIT_TIMESTAMP | REDO_SQL | メモ |
HR | 1029077 | 2017/03/12 19:07:35 | 2017/03/12 19:07:37 | update "HR"."CON_PART" set "NAME" = 'JDK' where "ABBR" = 'DJ' and "NAME" = 'DJDJD' and ROWID = 'AAAW8dAAEAAAAakAAA'; | 実表更新 |
SYS | 1029081 | 2017/03/12 19:07:41 | 2017/03/12 19:07:41 | insert into "HR"."J$CON_PART"("WINDOW_ID","ABBR") values ('1029077','DJ'); | ジャーナル表へSCNとPKを挿入 |
SYS | 1029081 | 2017/03/12 19:07:41 | 2017/03/12 19:07:41 | insert into "SYS"."STREAMS$_APPLY_PROGRESS"("APPLY#","SOURCE_DB_NAME","XIDUSN","XIDSLT","XIDSQN","COMMIT_SCN","SPARE1","COMMIT_POSITION","TRANSACTION_ID") values ('1','ORCL','3','7','1310','1029077',NULL,HEXTORAW('0000000fb3d500000001000000010000000fb3d5000000010000000101'),NULL); | 上記SCN(1029077)を登録。ただし、この表のSCNはジャーナルビューには連携しない |
SYS | 1029115 | 2017/03/12 19:08:11 | 2017/03/12 19:08:11 | update "SYS"."STREAMS$_APPLY_MILESTONE" set "OLDEST_SCN" = '1029077', "COMMIT_SCN" = '1029077', "SYNCH_SCN" = '0', "EPOCH" = '4', "PROCESSED_SCN" = '1029091', "APPLY_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR'), "APPLIED_MESSAGE_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR'), "SPARE1" = '0', "START_SCN" = '1029113', "OLDEST_TRANSACTION_ID" = '3.7.1310', "LWM_EXTERNAL_POS" = NULL, "OLDEST_POSITION" = NULL, "PROCESSED_POSITION" = NULL, "START_POSITION" = NULL, "XOUT_PROCESSED_POSITION" = NULL, "XOUT_PROCESSED_CREATE_TIME" = NULL, "XOUT_PROCESSED_TID" = NULL, "XOUT_PROCESSED_TIME" = NULL, "APPLIED_HIGH_POSITION" = NULL, "OLDEST_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR'), "SPARE5" = NULL, "PTO_RECOVERY_SCN" = '875335', "PTO_RECOVERY_INCARNATION" = '1' where "OLDEST_SCN" = '1029075' and "COMMIT_SCN" = '1024949' and "SYNCH_SCN" = '0' and "EPOCH" = '4' and "PROCESSED_SCN" = '1029077' and "APPLY_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR') and "APPLIED_MESSAGE_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR') and "SPARE1" = '0' and "START_SCN" = '1029078' and "OLDEST_TRANSACTION_ID" = '3.7.1310' and "LWM_EXTERNAL_POS" IS NULL and "OLDEST_POSITION" IS NULL and "PROCESSED_POSITION" IS NULL and "START_POSITION" IS NULL and "XOUT_PROCESSED_POSITION" IS NULL and "XOUT_PROCESSED_CREATE_TIME" IS NULL and "XOUT_PROCESSED_TID" IS NULL and "XOUT_PROCESSED_TIME" IS NULL and "APPLIED_HIGH_POSITION" IS NULL and "OLDEST_CREATE_TIME" = TO_DATE('12-MAR-17', 'DD-MON-RR') and "SPARE5" IS NULL and "PTO_RECOVERY_SCN" = '875335' and "PTO_RECOVERY_INCARNATION" = '1' and ROWID = 'AAAANKAABAAABbJAAA'; | 上記SCN(1029077)に更新。この表のSCNが、ジャーナルビューに連携(WHERE句で使用)される。以降、30secおきに、ほぼ同様のSQLが発行されている模様。 |
update HR.SNP_CDC_SET set CUR_WINDOW_ID= NVL((select APPLIED_MESSAGE_NUMBER from ALL_APPLY_PROGRESS where apply_name = 'ODI_CDC_A'), 0), CUR_WINDOW_ID_DEL= NVL((select APPLIED_MESSAGE_NUMBER from ALL_APPLY_PROGRESS where apply_name = 'ODI_CDC_A'), 0), CUR_WINDOW_ID_INS= NVL((select APPLIED_MESSAGE_NUMBER from ALL_APPLY_PROGRESS where apply_name = 'ODI_CDC_A'), 0) where CDC_SET_NAME = 'HR.CONCON'
update HR.SNP_CDC_SUBS SUBS set ( SUBS.MAX_WINDOW_ID, SUBS.MAX_WINDOW_ID_INS, SUBS.MAX_WINDOW_ID_DEL ) = ( select CDC.CUR_WINDOW_ID, CDC.CUR_WINDOW_ID_INS, CDC.CUR_WINDOW_ID_DEL from HR.SNP_CDC_SET CDC where CDC.CDC_SET_NAME = 'HR.CONCON' ) where SUBS.CDC_SET_NAME = 'HR.CONCON' and SUBS.CDC_SUBSCRIBER = ?
select CON_PART.ABBR AS ABBR, CON_PART.NAME AS NAME, CON_PART.LAND_KM AS AREA_KM from HR.JV$CON_PART CON_PART where JRN_FLAG <> 'D' AND JRN_SUBSCRIBER = 'SUNOPSIS' /* AND JRN_DATE < sysdate */
update HR.SNP_CDC_SUBS SUBS set SUBS.MIN_WINDOW_ID = SUBS.MAX_WINDOW_ID_INS where SUBS.CDC_SET_NAME = 'HR.CONCON' and SUBS.CDC_SUBSCRIBER = ?
delete from HR.J$CON_PART JRN where JRN.WINDOW_ID <= ( select min(SUBS.MIN_WINDOW_ID) from HR.SNP_CDC_SUBS SUBS where SUBS.CDC_SET_NAME = 'HR.CONCON' )
ALTER TABLESPACE <テーブルスペース名> FORCE LOGGING;
SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- NO SQL> ALTER DATABASE FORCE LOGGING; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES