Home » RDBMS Server » Networking and Gateways » ORA-12514 on accessing table through DBLINK (WINDOWS)
ORA-12514 on accessing table through DBLINK [message #355107] Wed, 22 October 2008 12:53 Go to next message
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.
Re: ORA-12514 on accessing table through DBLINK [message #355109 is a reply to message #355107] Wed, 22 October 2008 13:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you able to do this from the SERVER?

Regards
Michel
Re: ORA-12514 on accessing table through DBLINK [message #355112 is a reply to message #355109] Wed, 22 October 2008 13:13 Go to previous messageGo to next message
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?
Re: ORA-12514 on accessing table through DBLINK [message #355117 is a reply to message #355112] Wed, 22 October 2008 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-12514 on accessing table through DBLINK [message #355153 is a reply to message #355117] Wed, 22 October 2008 23:01 Go to previous messageGo to next message
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?
Re: ORA-12514 on accessing table through DBLINK [message #355155 is a reply to message #355153] Wed, 22 October 2008 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-12514 on accessing table through DBLINK [message #355160 is a reply to message #355155] Wed, 22 October 2008 23:21 Go to previous messageGo to next message
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
Re: ORA-12514 on accessing table through DBLINK [message #355166 is a reply to message #355160] Wed, 22 October 2008 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: ORA-12514 on accessing table through DBLINK [message #493225 is a reply to message #355160] Sun, 06 February 2011 22:25 Go to previous messageGo to next message
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?
Re: ORA-12514 on accessing table through DBLINK [message #493227 is a reply to message #493225] Sun, 06 February 2011 22:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
CREATE DATABASE LINK MY_DBLINK CONNECT TO USER1 IDENTIFIED BY PASSWORD1 USING 'TNS_ALIAS';
Re: ORA-12514 on accessing table through DBLINK [message #493239 is a reply to message #493225] Mon, 07 February 2011 00:47 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: ORA-12514 on accessing table through DBLINK [message #493420 is a reply to message #493227] Mon, 07 February 2011 18:43 Go to previous message
rickh100
Messages: 4
Registered: April 2005
Location: Staten Island
Junior Member
Very Intuitive. I never made that connection.

Works fine. Thanks for your help.
Previous Topic: Connectivity between MS-ACCESS AND ORACLE SQL
Next Topic: Connecting to database out side the network
Goto Forum:
  


Current Time: Fri Mar 29 04:43:33 CDT 2024