Oracle - Db links

Posted on 2006-11-23
Medium Priority
Last Modified: 2008-01-09

I have many dblinks to various databases. Each hour one db job is running which
    uses these dblinks. But sometimes happens that some changes are made (e.g. IP
    address change) and then the dblink is unusable. But the job is hanging with
    "SQL*Net message from dblink" wait event for a very long time.
    Is there some possibility to set some "dblink timeout" or to simply check the
    dblink status before?

Question by:expertblr
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 18006167
i think the easiest way : "select * from dual@link_db"
LVL 13

Expert Comment

ID: 18008060
select * from dba_db_links:

you can view the no of db links in the database,
Incase any changes in the ip address,you have to change the tns entry to refer the new ipaddress.

Once you change the ip address and you din change the tns entry then the db link will get invalid and the job will be hanging

Author Comment

ID: 18043193
Thanks for reply. Its perfectly clear to me that when some IP address of any
    server is changed then the dblink is unusable until the IP in tnsnames is
    changed as well. I've just wanted to ask you if is there any possibility to
    somehow handle this problem directly in plsql code. Now we are facing the
    problem, that something has chaged (IP....) but we don't know it immediatelly.
    We can only see after some time, that the session is hanging with wait for
    dblink and it's going to hang probably forever. So I would need only either to
    perform some quick functionality check of particular dblink in the beginning of
    the code or to set some "dblink timeout".

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Author Comment

ID: 18051488
can anyone suggest an answer to this??? Please.....

Author Comment

ID: 18106751
please help me out!!
LVL 13

Accepted Solution

anand_2000v earned 1000 total points
ID: 18120345
in the tnsnames.ora use the machine name not the ipaddress.

Expert Comment

ID: 20296243
Forced accept.

EE Admin

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question