how to create database link in oracle using static ip

Posted on 2009-02-17
Last Modified: 2013-12-19
how to create database link in oracle using static ip

Question by:anup1109
    LVL 73

    Expert Comment

    do you know the port and service/sid name?

    if so, add a definition to your database SERVER's tnsnames.ora like this... =
        (ADDRESS = (PROTOCOL = TCP)(HOST = = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = your_service_name)

    then create a database link inside the database like...

    create database link "your_db_link"
    connect to your_user
    identified by "your_password"
    using 'your_db_name'
    LVL 73

    Accepted Solution

    you can also put the entire tns entry in the link itself

    create database link your_db_link
    connect to your_user
    identified by your_password
    LVL 11

    Expert Comment

    Anup, DB links and IPs have an intermediate layer in between: TNS
    So your question ends up being two-part:
    • How do I build up a TNS address using a static IP?
    • How do I use it to create a DB link?
    For the first question, you just use your static IP where you would put the host name.
    For the second, you create a DB link referencing the TNS alias, or else specify the TNS connection string in place. Both things are done through the keyword USING (sdstuber's 1st and 2nd examples respectively).
    Note that you can omit the CONNECT TO ... IDENTIFIED BY ... portion. In that case, remote connections will be attempted with the same credentials you used to connect to your local DB.
    Again, it's NOT that DB links have static IPs... it's TNS connection strings that have them :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    sysaux tablespace 3 34
    Format Number Field 10 34
    Number Format 1 30
    CREATE TABLE syntax 4 32
    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now