Friday, February 8, 2013

8 Oracle Data Link trouble shooting

I am testing the Oracle data link. This post is to describe the issue and trouble shooting.


List all the data link

Query all_db_links table.

select *  from all_db_links


Create data link

When create the data link, we need to provide the connection string . The connection string is best to defined   in the tnsname.ora for easy maintained . However, to eliminate the problem about the tnsname.ora, we can embodied the entire connection string in the SQL statement .


Below is the example to create the public data link . The data link name is “TEST_LINK”. Oracle would append the db_domain when query the db_links table.




Below example is moving the full connection string to the tnsname.ora.

create database link TEST_LINK2 using 'ORADB2.panda2.localdomain';



Below example is to create the full Databsae link name with remote database . This is necessary if global_names set to true on either of the source or target, otherwise, we would get the ORA-02085 error.

create database link ORADB2.panda2.localdomain using 'ORADB2.panda2.localdomain';



How to resolve ORA-02085 error

This error is because the global_names. When connect to the data link, if global names parameter set to true on the local database, Oracle would expect the Data link name same as the remote global name.


ORA-02085: database link TEST_LINK2.PANDA1.LOCALDOMAIN connects to


The data link name is the real name show up ALL_DB_LINKS table , DB_LINK column. Global name can be found in the global_name table.

show parameter global_names
select * from global_name;
select DB_LINK from ALL_DB_LINKS;



Here are solutions

1. Turn off the global names: In some case, this may not be doable or desire

2. Turn off global names in the session level


3. The best way is to create the DB_LINK name that is the remote database’s global name

create database link ORADB2.panda2.localdomain using 'ORADB2.panda2.localdomain';




How to resolve ORA-01017 error

This error message indicate the authentication fail.

ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from TEST_LINK2


Here are possible reasons:

For the private database link, if login/password is not defined during the create database link, that mans it is using the current user’s credential . We have to make sure the remote database has the same account and password set.

If the private database link is create by sys, the connection would fail because sys has to login as sysdba and the database link connection can not specify the sysdba role.





  1. Getting blog comments on your site is always a good indication that the niche blog comments reader is involved in what it is you are posting about. Good bad or indifferent this is a good barometer that your blog posting is not a wasted effort. The interactivity of your readers is even more important if you are running a home business blog.

  2. A little-known free way to get tons of targeted visitors is via blog comments. Commenting on niche guest post can be fun, and it's a great way to drive traffic for your business!

  3. wow... what a great blog, this writter who wrote this article it's realy a great blogger, this article so inspiring me to be a better person OpenVZ VPS Hosting

  4. Thank you because you have been willing to share information with us. we will always appreciate all you have done here because I know you are very concerned with our. Odzyskiwanie danych Łódź

  5. Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long. I need this article to complete my assignment in the college, and it has same topic with your article. Thanks, great share. odzyskiwanie danych Warszawa

  6. If you're a click this site owner, this article will discuss why you should be insuring your company vehicles. Anytime your company vehicle is on the road, it is at risk.

  7. This site is really a walk-through for all of the data you wanted about it and didn’t know who to ask. Glimpse here, and you’ll undoubtedly discover it. Pest Control In Abu Dhabi



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates