Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Materialized View -> synonym -> other user -> dblink -> remote db

gutierrezgeSep 7 2012 — edited Sep 8 2012
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?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 5 2012
Added on Sep 7 2012
7 comments
1,772 views