Ora 12154 Dblink Error
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers ora 12154 tns could not resolve the connect identifier specified 11g or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Ora-02085
Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only ora-02019: connection description for remote database not found takes a minute: Sign up on using oracle database link theres an error “ORA-12154 TNS Could not resolve the connect identifier specified” up vote 0 down vote favorite I'm trying to define a database link on oracle 10.2 with connection create dblink oracle identifier that throws the error in the question header. I have the connection identifier (service name) in my tnsnames.ora file. I can connect with sqlplus using this service name. no problem. This is the creation sql: create database link dev1.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to user1 identified by pass1 using 'dev1'; select using the link: select * from t_users@dev1; I get: ORA-12154: TNS:could not resolve the connect identifier specified connecting to oracle with sqlplus to the 'unidentified' service (with no problem): sqlplus user1/pass1@dev1 I defined another link to the same database I'm woking in (loopback) - works OK. I read and tried anything I could find about the subject but did not solve this. Any suggestions? oracle database-link share|improve this question edited May 4 '12 at 0:57 BrettAdamsGA 60311122 asked May 2 '12 at 15:36 gold finger 11112 Did you configure SQLNET? If you're using Local naming you may have to add tnsnames to the NAMES.DIRECTORY_PATH in your Oracle Net Profile. You should post both your tnsnames and sqlnet files. –apesa May 2 '12 at 15:50 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote Try: create database link dev1 connect to user1 identified by pass1 using 'dev1'; Also note that a tnsnames.ora entry for dev1 has to exist on the database server that you're linking from. share|improve this answer answered May 2 '12 at 15:39 Phil 1,6601014 thanks Phil. You nailed it with your second note. –gold finger May 3 '12 at 10:37 It's so obvious I'm quite ashamed I didn't think of it. The tnsnames I refered to was of course on my workstation. Thank you very much. –gold finger May 3 '12 at 10:40 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as a guest Name Ema
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Oracle: http://stackoverflow.com/questions/10417010/on-using-oracle-database-link-theres-an-error-ora-12154-tns-could-not-resolve-t Inserting data using a database link up vote 0 down vote favorite I am trying to use a SELECT query to populate a table in DB2 using a database link to DB1. I am using Oracle SQL Developer on a Windows 7 VM. I connect to DB2, and run the following query. INSERT INTO DB_NAME.TABLE_NAME (SELECT * FROM DB_NAME.TABLE_NAME@LINK_TO_DB1); Every time, no matter what, I get the following error: ORA-12154: TNS:could not resolve the connect identifier specified 12154. http://dba.stackexchange.com/questions/93160/oracle-inserting-data-using-a-database-link 00000 - "TNS:could not resolve the connect identifier specified" *Cause: A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached. *Action: - If you are using local naming (TNSNAMES.ORA file): - Make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA) - Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible. - Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file. - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable. - If you are using directory naming: - Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA). - Verify that the LDAP directory server is up and that it is accessible. - Verify that the net service name or database name used as the connect identifier is
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your https://community.oracle.com/thread/2188088 message and try again. More discussions in General Database Discussions All PlacesDatabaseGeneral Database Discussions http://www.club-oracle.com/threads/a-question-about-create-public-database-link-and-ora-12154-error.1898/ This discussion is archived 9 Replies Latest reply on Mar 7, 2011 5:58 PM by EdStevens Got error: ORA-12154 when to use a dblink 836048 Mar 4, 2011 7:52 PM Hi, I got an error when I use a dblink created in the database: dev_nap. *** login OSSP database (oracle 9i) in sqlplus SQL> select * from global_name; ora 12154 GLOBAL_NAME ------------------------------------- OSSP *** login dev_nap (oracle 10g 10.2) by sqlplus SQL> select * from global_name; GLOBAL_NAME -------------------------------- DEV_NAP.OPBU.XEROX.COM SQL> CREATE DATABASE LINK OSSP 2 CONNECT TO usog01001 IDENTIFIED BY jan25hc USING 'OSSP'; Database link created. SQL> select count(*) from TK_SERVICE_ACTIVITY@OSSP; Above info, I login with the same sqlplus. Thanks Helen I have the same question Show 0 Likes(0) 412Views Tags: none (add) databaseContent tagged with database, linkContent tagged with link, oracleContent tagged ora 12154 dblink with oracle This content has been marked as final. Show 9 replies 1. Re: Got error: ORA-12154 when to use a dblink sb92075 Mar 4, 2011 7:55 PM (in response to 836048) post content of tnsnames.ora Like Show 0 Likes(0) Actions 2. Re: Got error: ORA-12154 when to use a dblink 836048 Mar 4, 2011 8:32 PM (in response to sb92075) DEV_NAP_TRIBBLE.CESA.OPBU.XEROX.COM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = tribble.cesa.opbu.xerox.com)(PORT = 1521)) ) (CONNECT_DATA = (SID = dev_nap) (SERVER = DEDICATED) ) ) OSSP = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ossp_svr.wvdc.xerox.net)(PORT = 1527)) ) (CONNECT_DATA = (SID = ossp) (SERVICE_NAME = ossp) (DB_NAME = ossp) ) ) Like Show 0 Likes(0) Actions 3. Re: Got error: ORA-12154 when to use a dblink sb92075 Mar 4, 2011 8:35 PM (in response to 836048) post content of sqlnet.ora file, if it exists Like Show 0 Likes(0) Actions 4. Re: Got error: ORA-12154 when to use a dblink 836048 Mar 4, 2011 9:08 PM (in response to sb92075) SQLNET.AUTHENTICATION_SERVICES= (NTS) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) Like Show 0 Likes(0) Actions 5. Re: Got error: ORA-12154 when to use a dblink sb92075 Mar 4, 2011 9:13 PM (in response to 836048) I got an error when I use a dblink created in the datab
get a Premium Badge! A question about CREATE PUBLIC DATABASE LINK and ORA-12154 error Discussion in 'Server Administration and Options' started by dariyoosh, Nov 17, 2009. dariyoosh Forum Advisor Messages: 118 Likes Received: 19 Trophy Points: 260 Dear all, I have a problem about public database creation and I would appreciate if you could kindly give me a hand. I have the following connection parameters in my tnsnames.ora file: Code (Text): ... DGPAPROD.WORLD = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY=tcp.world) (PROTOCOL=TCP)(HOST=ORASR001)(PORT=1521) ) ) (CONNECT_DATA = (SID = DGPAPROD)) ) ... Having the above mentioned parameters I can connect to this remote database directly in a SQL*Plus shell: Code (Text): $ sqlplus username/password@DGPAPROD.WORLD This works pretty well and the connection is established without any problem. Now, what I would like to do is to create a public database link to this remote database in order to avoid the user/connection switching for viewing the content of this database. I proceeded according to the syntax indicated in the Oracle online documentation: http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_5005.htm#SQLRF01205 Therefore I run the following in order to create a public database link Code (Text): CREATE PUBLIC DATABASE LINK SR001_dblink CONNECT TO user IDENTIFIED BY password USING 'DGPAPROD.WORLD'; Apparently there is no error and the link is created successfully. However it cannot resolves the remote host and whenever I run the following query Code (Text): SELECT * FROM myenterprise@SR001_dblink ERROR at line 1: ORA-12154: TNS:could not resolve the connect identifier specified What causes this problem? Thanks in advance, Kind Regards, Dariyoosh dariyoosh, Nov 17, 2009 #1 Kirti Forum Advisor Messages: 46 Likes Received: 12 Trophy Points: 130 Hi In which database are you creating this Database Link? On the database where you are creating this DB link, is DGPAPROD.WORLD present in the tnsnames.ora? Alternately you can bypass tnsnames and create the DB link directly using the hostname and port and SID, something like this: Code (SQL): CREATE DATABASE link "TEST" CONNECT TO APPS IDENTIFIED BY "apps" USING '10.31.4.121:1524/DEVR12' Kirti, Nov 18, 2009 #2 dariyoosh Forum Advisor Messages: 118 Likes Received: 19 Trophy Points: 260 Kirti said: ↑ Hi In which database are you creating this Database Link? On the database where you