Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Madhan.

Asked: November 07, 2019 - 5:32 pm UTC

Answered by: Chris Saxon - Last updated: October 27, 2020 - 1:46 am UTC

Category: Database Development - Version: Oracle 12c

Viewed 1000+ times

You Asked

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.

and we said...

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

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

November 08, 2019 - 3:30 pm UTC

Reviewer: Madhan Subbiah from PA

Thanks for your quick reply.

We were using "Change Data Capture" in our application to track changes and once process the changes, we purge those from Change Data Capture Table and we do this once in every 3 hours.

Since Change Data Capture is de-supported in 12c, we are planning to use alternative like Mview Log or Trigger till we convert/move completely to Oracle Golden Gate. As you suggested,please let us know if we can explore "Flashback Data Archive" option for replacement of "CDC" till we move to Oracle Golden Gate.

Thanks
Connor McDonald

Followup  

November 11, 2019 - 1:27 am UTC

Flashback data archive lets you see every version of a row from its inception. So of that is what you are after, then it might be useful.

This might help explain things better


Flash back

October 23, 2020 - 9:32 pm UTC

Reviewer: Madhan Subbiah from PA USA

Version:12.1.0.2.0
Step1:create flashback archive longterm tablespace CR_DATA01 retention 10 day
Step2: alter table pr_site flashback archive longterm
Step3:
update pr_site set create_dt =sysdate where pr_site_id=1110576442
commit
Step4:
select * from pr_site
versions between timestamp
SYSTIMESTAMP-(1/24) AND SYSTIMESTAMP

Problem:
Have nt got any error in above all steps but
Step3 updated record not coming in output of Step4. Instead of, it is brining all records from pr_site

Can you please assist if any wrong in this ?
Connor McDonald

Followup  

October 27, 2020 - 1:46 am UTC

What happens with

select * from pr_site
versions between scn minvalue and maxvalue;



More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.