Querying remote database via database link
Posted on 2013-06-03
I need to get data from RemoteDB1 while I am logged in to SourceDB. I found that there are two database links previously created on SourceDB to RemoteDB1, called SourceDB_TO_RemoteDB and RemoteDB.
When I queried dba_db_links, I saw that OWNER for both links is PUBLIC, and username for both links is RemoteDB (same as the GLOBAL_NAME in the RemoteDB).
RemoteDB user does not exist in RemoteDB1 but not in SourceDB.
Now I also have user RPT in RemoteDB1 database that has a view in its schema Main_view. Main_view was created on a couple of catalog views that user RPT has SELECT privileges on.
SourceDB also has a user RPT created.
I was trying to use datbase link RemoteDB while logged in to the SourceDB to access Main_view in RPT user schema:
SELECT FIELD1 "Field 1", FIELD2 "Field 2"
WHERE START_TIME > SYSDATE-1
ORDER BY FIELD1;
I get an error
ERROR at line 2:
ORA-00942: table or view does not exist
ORA-02063: preceding line from RemoteDB
I have gotten this error both, while logged in as RPT user and as SYS user.
Can someone help me to query data from RemoteDB1?