Thanks Slimdave. I was making two mistakes
1. M. View log created without the option "WITH ROWID"
2. ROWIDs were not included in the select list.
This is my R&D result which is going to implement in UAT soon.
ThomasCode:create table emp (empno number, deptno number, name varchar2(20)) ; create table dept (deptno number, deptname varchar2(20)); alter table emp add constraint empno_pk primary key (empno); alter table dept add constraint deptno_pk primary key (deptno); create MATERIALIZED VIEW LOG ON emp WITH ROWID; create MATERIALIZED VIEW LOG ON dept WITH ROWID; CREATE MATERIALIZED VIEW emp_dep TABLESPACE users STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0 ) REFRESH fast AS select e.empno,e.deptno,e.name,d.deptname,d.rowid drid,e.rowid erid,d.deptno ddeptno from emp e ,dept d where e.deptno = d.deptno;




Reply With Quote