Materialized View -> synonym -> other user -> dblink -> remote db
Hi, I'm trying to generate a materialized view
CREATE MATERIALIZED VIEW My_MV
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
START WITH TO_CHAR(TRUNC(SYSDATE) + 22/24, 'MM/DD/YYYY HH:MI:SS')
NEXT SYSDATE + 1
WITH PRIMARY KEY
AS SELECT * FROM My_RealView
but I'm getting this error
Error at Command Line:7 Column:30
Error report:
SQL Error: ORA-04052: error occurred when looking up remote object remoteSchema.MyRealView@db_link_to_another_db
ORA-00604: error occurred at recursive SQL level 1
ORA-02019: connection description for remote database not found
04052. 00000 - "error occurred when looking up remote object %s%s%s%s%s"
*Cause: An error has occurred when trying to look up a remote object.
*Action: Fix the error. Make sure the remote database system has run
KGLR.SQL to create necessary views used for querying/looking up
objects stored in the database.
So how this works, is that I want to make a MV out of a Synonym (My_RealView) but this synonym uses a DB Link from another user (read only access), which connects to an external database and reads the info from a view.
I was wondering if this is even possible or should i have the db link exposed under the same schema.
The reason for this is separation of duties and security.
I do not know what is the configuration for the db link nor where I'm getting connected, i just know is an oracle db.
This db link is under another user, is because they don't want us to be able to mess with it (company policies)
Will I be able to generate a MV with this type of architecture?