Thanks Connor McDonald, it worked after granting access to the user as suggested by you.
Just wanted to know one more thing, can we join more than one remote table (using dblink) while creating MV FAST REFRESH ON DEMAND WITH PRIMARY KEY.
CREATE materialized view HB_SYSTEM
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
WITH PRIMARY KEY
AS
SELECT * FROM HB.HB_SYSTEM@HBLINK hbsys
LEFT OUTER JOIN HB.HB_SUB@HBLINK hbsub ON hbsys.ID = hbsub.HB_sys_Id;
We have also created MV Logs on source(remote table) HB_SYSTEM and HB_SUB.
I am getting as below when i tried to run this above query.
SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query
Kindly suggest how can we create MV Fast Refresh with joins.
One more suggestion please, is it possible to join source tables(using dblinks) with local existing tables/MVs. For example,
CREATE materialized view HB_SYSTEM
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
WITH PRIMARY KEY
AS
SELECT * FROM HB.HB_SYSTEM@HBLINK hbsys
LEFT OUTER JOIN HB.HB_SUB hbsub ON hbsys.ID = hbsub.HB_sys_Id
LEFT OUTER JOIN DSHB.MV_HB_Order Ord ON Ord.HB_sys_Id = hbsys.ID;