HelpDesk
sign in

How to enable CDC on Views - Oracle | Source Lookup Alternative

Creation date: 08/10/2020 10:10    Updated: 16/02/2021 15:16    cdc from view oracle view replication
Print...
Replicating views from Oracle source:
For this, we need to create a Materialized view (MV) with a join on the tables. Query is given below for your reference. There might be more tweaks possible on the below MV log definition and MV definition to make it more efficient. Please make sure to run this by the Oracle DBA’s and ensure that they are onboard with the concept.

--Step1: CREATE Materialized View Log on Table1
--Step2: CREATE Materialized View Log on Table2
--Step3: Create Materialized View with inner join on Table1 and Table2

CREATE MATERIALIZED VIEW LOG ON C##NORTHWIND.ORDERS
       PCTFREE 5
       --TABLESPACE example
       STORAGE (INITIAL 10K NEXT 10K)
       WITH PRIMARY KEY, ROWID, SEQUENCE(CUSTOMERID, EMPLOYEEID, SHIPNAME, SHIPADDRESS, SHIPCITY, SHIPREGION)
       INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON C##NORTHWIND.CUSTOMERS
       PCTFREE 5
       --TABLESPACE example
       STORAGE (INITIAL 10K NEXT 10K)
       --WITH ROWID, SEQUENCE(CUSTOMERID, COMPANYNAME )
       WITH PRIMARY KEY, ROWID, SEQUENCE(COMPANYNAME )
       INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW C##NORTHWIND.VW_MAT_JOINVIEW_ORDERS
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
   AS SELECT O.ROWID ORID, O.ORDERID, O.CUSTOMERID, O.EMPLOYEEID, O.SHIPNAME, O.SHIPADDRESS, O.SHIPCITY, O.SHIPREGION,C.COMPANYNAME,C.ROWID CRID
         FROM C##NORTHWIND.ORDERS O INNER JOIN C##NORTHWIND.CUSTOMERS C
         ON O.CUSTOMERID=C.CUSTOMERID

When the table ORDERS was updated with the below query it was showing the correct results in the target object (C##NORTHWIND.VW_MAT_JOINVIEW_ORDERS) similar to that of a lookup. The only difference that I could see is that in change table, the operation identified is a “DELETE -> INSERT” instead of an UPDATE for the MV.

UPDATE C##NORTHWIND.ORDERS
SET CUSTOMERID = 'LILAS'
WHERE ORDERID = 10498;
COMMIT;

UPDATE C##NORTHWIND.ORDERS
SET CUSTOMERID = 'HILAA'
WHERE ORDERID = 10498;
COMMIT;



In the same task I’ve included the ORDERS table as well with a SOURCE lookup on CUSTOMERS table just to compare the results.


Result using Materialized Views



Result using Source Lookup





Thanks and Regards,

Jijo James
Integrated Business Technologies Pty Ltd (IBT)
Level 4/84 Pitt St, Sydney, NSW, 2000
Tel/Direct: (02) 8205 3917 | (04) 3952 4432

NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information or otherwise be protected by law. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message


OriginalEmail.eml
Materialized Views - Bendigo.sql
image013.jpg
image012.jpg
image007.jpg
image006.jpg
image004.jpg
Files
Sort by Name Sort by Date
Get help for this page