Db Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
desc Par_trials;
Column Name Pk Null? Data Type
PARTY_TRIAL_ID 1 N NUMBER (11)
PARTY_ID N NUMBER (11)
PARTY_ROLE_TYPE_ID N NUMBER (11)
CLINICAL_TRIAL_ID N NUMBER (11)
START_DT Y DATE
END_DT Y DATE
CORE_CREATE_DT N DATE
CORE_LAST_UPDT Y DATE
EDC_FLAG Y VARCHAR2 (1 Byte)
CPAC_FLAG Y VARCHAR2 (1 Char)
ELMS_FLAG Y VARCHAR2 (1 Char)
CREATE MATERIALIZED VIEW LOG ON par_trials
WITH ROWID, SEQUENCE (party_id,party_role_type_id,clinical_trial_id,start_dt,end_dt,core_create_dt,core_last_updt,edc_flag,cpac_flag,elms_flag), PRIMARY KEY
INCLUDING NEW VALUES
Insert into PAR_TRIALS
(PARTY_TRIAL_ID, PARTY_ID, PARTY_ROLE_TYPE_ID, CLINICAL_TRIAL_ID, START_DT, CORE_CREATE_DT)
Values
(365352, 50858, 528, 1055, TO_DATE('10/30/2019 20:53:51', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/07/2019 12:03:59', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
update par_trials set core_create_dt=sysdate
where party_Trial_id=365352;
select * from MLOG$_PAR_TRIALS
Question:
1) Would like to include a new column called "OPERATION$" in MLOG$_PAR_TRIALS table which should have below column values
"UU" which indicates before update and should contain all its corresponding old values
"UN" which indicates after update and should contain all its corresponding new values
2) In mlog creation script, have included "INCLUDING NEW VALUES" and i do have requirement to "exclude old values". Expectation here is just to retain only new values in mlog. Is that possible? If yes, kindly share example.
3) Can we do delete operation against MLOG$_PAR_TRIALS?If yes, kindly share example.
1. No
You've defined the log as INCLUDING NEW VALUES. So it already contains the old and new values after updates. Search for
where DMLTYPE$$ = 'U' and OLDNEW$$ = 'N'
To find the new values. And change OLDNEW$$ to O to find the previous values.
2. No
3. No
Well, strictly speaking you
can delete rows from the MV log. But doing so compromises your fast refreshes. So you shouldn't!
It sounds like you're trying to use an MV log as some kind of audit log or change history. That's not what it's for.
Their purpose is to track changes for tables you use in materialized views. So the database can use these to do a fast refresh. i.e. just apply the last few insert/update/deletes to the MV, instead of having to rerun the full query from scratch.
If you are looking for some kind of change tracking, you should check out Flashback Data Archive:
https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/flashback.html#GUID-06AA782A-3F79-4235-86D5-2D136485F093