OraFAQ Forum: Networking and Gateways » ORA-12514 on accessing table through DBLINK

Home » RDBMS Server » Networking and Gateways » ORA-12514 on accessing table through DBLINK (WINDOWS)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-12514 on accessing table through DBLINK [message #355107]

Wed, 22 October 2008 12:53

swapnarao1
Messages: 4
Registered: October 2008

Junior Member

Hi,

I have a database ORCL on my local windows system. I have an entry for a remote database (RIDB) on Solaris box. The tnsnames for the same has been made in my local tnsnames.ora file. I am able to tnsping to this remote database, also able to connect via sqldeveloper. I created a dblink for this remote database from my local database. The DBlink creation is successful although access the table in the remote DB using dblink results in the error ORA-12514. Any idea what needs to be checked here?

Pointers if this matters:

1. The gloabl names of the remote and the local DB are different.

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355109 is a reply to message #355107]

Wed, 22 October 2008 13:04

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Are you able to do this from the SERVER?

Regards
Michel

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355112 is a reply to message #355109]

Wed, 22 October 2008 13:13

swapnarao1
Messages: 4
Registered: October 2008

Junior Member

I am able to ping the remote DB from my local windows machine. Is that what you are referring to?

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355117 is a reply to message #355112]

Wed, 22 October 2008 13:59

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, are you able to connect to remote database from local database with the user that started the local database?

Regards
Michel

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355153 is a reply to message #355117]

Wed, 22 October 2008 23:01

swapnarao1
Messages: 4
Registered: October 2008

Junior Member

Hi,

Actually I do not have access as a system user on the remote DB. I am trying to connect as one of the users of the remote DB. Is it a requirement to connect to the remote DB only as a user who has started it?

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355155 is a reply to message #355153]

Wed, 22 October 2008 23:06

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

When you use a database link this is the database (and not you) with the environment of the user that started it that connects to the target database, so my question.

Regards
Michel

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355160 is a reply to message #355155]

Wed, 22 October 2008 23:21

swapnarao1
Messages: 4
Registered: October 2008

Junior Member

Thanks Michael,

Not really sure if I answered what you asked. I am doing this for the first time and hence need a little more help on it.

Regards,
Swapna

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #355166 is a reply to message #355160]

Wed, 22 October 2008 23:30

Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to (OS) connect on the server that contains the instance (database) with the user that started it and then try to (SQL*Plus) connect to the remote database using the same connection string that is in your database link.

Regards
Michel

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #493225 is a reply to message #355160]

Sun, 06 February 2011 22:25

rickh100
Messages: 4
Registered: April 2005
Location: Staten Island

Junior Member

Worked for me but I don't understand why. This issue has been driving me crazy for sometime. Are you saying the link looks at the tnsnames file on the server?

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #493227 is a reply to message #493225]

Sun, 06 February 2011 22:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

CREATE DATABASE LINK MY_DBLINK CONNECT TO USER1 IDENTIFIED BY PASSWORD1 USING 'TNS_ALIAS';

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #493239 is a reply to message #493225]

Mon, 07 February 2011 00:47

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

rickh100 wrote on Mon, 07 February 2011 05:25

Are you saying the link looks at the tnsnames file on the server?

Yes, on the server.

It is easy to test it - create a database link that uses a database alias from TNSNAMES.ORA that exists on a client (your own computer), but doesn't exist in TNSNAMES.ORA that resides on a server. Database link will be created, but you won't be able to use it:

SQL> create database link dbl_brisime
  2  connect to blabla
  3  identified by xyz
  4  using 'does_not_exist';

Database link created.

SQL> select 'x' from dual@dbl_brisime;
select 'x' from dual@dbl_brisime
                     *
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified


SQL>

Report message to a moderator

Re: ORA-12514 on accessing table through DBLINK [message #493420 is a reply to message #493227]

Mon, 07 February 2011 18:43

rickh100
Messages: 4
Registered: April 2005
Location: Staten Island

Junior Member

Very Intuitive. I never made that connection.

Works fine. Thanks for your help.

Report message to a moderator

Previous Topic:	Connectivity between MS-ACCESS AND ORACLE SQL
Next Topic:	Connecting to database out side the network

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 07 21:17:44 CST 2025