Re: Can't see remote table

Sorry, there was a typo in my original post. Both schemas are on database TC6. What I am looking to do is to create a link which allows me to query tables from two schemas on the same remote database. I am connected to the local database as system.

create public database link tc6 using '<connection info copied from tnsnames.ora>' ;
select * from dual_at_tc6;

gets "ORA-01017: invalid username/password; logon denied".

If I add 'connect to "sys as sysdba" identified by <password>' I get the same error. I've checked with SQL*Plus that the password is correct.

If I connect as tc_0002 or tc_0005 then I get the errors as in my original post, i.e. ORA-04043 'object <object> does not exist' for anything not in the current user's schema.

If I create one dblink for user tc_0002 and another for user tc_0005 I get ORA-02085 if the dblink does not have the same name as the target database. If I follow the advice in note 210630.1 on this error and change the full tnsnames string back to "using 'tc6' " it still doesn't find the object due to version inconsistencies between the source and target databases (8.1.7 and 9.2.0.6 respectively).

How do I do this?

Thanks
Andy Received on Mon Nov 14 2005 - 05:48:37 CST