Good afternoon, everybody.
I'm trying to create a materialized view using two or more dblinks.
Something like this:
Select * from scott.dep@db_x, scott.emp@db_x;
When I try to create the materialized view with only one remote table, it works fine, but when I put the second
table, it starts to give the error ORA-02019.
Does anyone have a suggestion about what might be causing this error?
My first guess would be that there may be some missing access rights from the schema you are using. Check the schema from which you are creating the view and make sure that it has at least "select" access to both of the referenced tables.
My second guess would be that there may be a table alias that is not recognized. Make sure you are accessing the linked tables using their proper names from their home schema, rather than a synonym.
Not sure why are you getting the errors.join condition missing?
1.Try granting select privs on tables on remote site to current site.
2.Try specifying the columns names instead of '*'
3.Create a view(Select col1,col2 from scott.dep a, scott.emp b where a.deptno=b.deptno ) on remote site and access it.
The ORA-02019 error message says "connection description for remote database not found". Are you sure that you are using the same db link for both remote tables as your posting shows?
What happens when you query the table with the db link that causes the error on its own?
I tried all those suggestions, but without success.
I was able to create a normal view with the dblink, but when I try to create
a Materialized view, the problem persists.
I'm using this parameters to create the Materialized View:
CREATE MATERIALIZED VIEW Billy.Test
START WITH TO_DATE('12-nov-2012 12:35:29','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE + 1/24
WITH PRIMARY KEY
Does any one have more suggestions?
Thanks for the help so far.