This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 11, 2012 4:29 AM by 966855 RSS

Creating Database link

966855 Newbie
Currently Being Moderated
I want to create database link to connect to oracle database in UNIX server from windows server.

I created db link in windows server.But when i am querying table in database using db link,it is saying that invalid username/password.

Please tell me how to establish db link between databases in windows and unix servers
  • 1. Re: Creating Database link
    866990 Explorer
    Currently Being Moderated
    CREATE DATABASE LINK <link name>
    CONNECT TO <username remote database>
    IDENTIFIED BY <password remote database>
    USING '<REMOTE DATABASE NAME FROM TNSNAMES.ORA>';
    dont forget the single quotes around the remote database name.
    Furthermore, the database server where your database resides has to be able to reach the database you want to connect to.
    This means your target database has to be in TNSNAMES.ORA on the server, network connection between both servers must be available (firewall?).

    Alternatively, you can use a complete connect string instead of your remote database name.

    your statement will be like this (fill in hostname, port and sid for your remote database):
    CREATE DATABASE LINK <link name>
    CONNECT TO <username remote database>
    IDENTIFIED BY <password remote database>
    USING '(DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
       )
     (CONNECT_DATA =
       (SERVICE_NAME = <sid>)
     )';
    hope this helps,

    .Robin
  • 2. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    can you ellborate this <username remote database>.

    which username we have to select. is it sys or schema username created in remote database.

    I tried following

    create PUBLIC database link "APEXDB"
    connect to "PSEASYRUN" identified by "EASYRUN#123"
    using '(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.123.196)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = HR91DMO)
    )
    )'
    /

    here PSEASYRUN is schema created in apex database.In this way link is created.

    when i am querying following error is occuring

    SQL> select * from dual@APEXDB;
    select * from dual@APEXDB
    *
    ERROR at line 1:
    ORA-01017: invalid username/password; logon denied
    ORA-02063: preceding line from APEXDB

    Please tell me why this error is coming.

    Apart from this which privileges we have to give to user to connect?
  • 3. Re: Creating Database link
    952768 Journeyer
    Currently Being Moderated
    Can you connect with sqlplus?

    c:> sqlplus PSEASYRUN@HR91DMO

    give your password
  • 4. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    sqlplus PSEASYRUN/EASYRUN#123 @ HR91DMO

    I got this output

    SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 10 15:50:05 2012

    Copyright (c) 1982, 2010, Oracle. All rights reserved.


    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SP2-0310: unable to open file "HR91DMO.sql"
    SQL>

    please tell me detailed steps to create db link?

    tell me in which database we have to create user and we have to login to which database to create db link?
  • 5. Re: Creating Database link
    952768 Journeyer
    Currently Being Moderated
    you let some space between, so sqlplus thinks he has to execute a sql script.

    Please try, running this from your windows server (because you want to connect to your remote database)
    sqlplus PSEASYRUN@HR91DMO
    without spaces between PSEASYRUN@HR91DMO

    and type your password this time only to test everything.

    to check name resolving you can do:
    tnsping HR91DMO
    Edited by: specdev on 10-okt-2012 3:42
  • 6. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    I am able to connect to HR91DMO(remote db) with PSEASYRUN/EASYRUN#123

    i created db link like this in local db by logging as sqlplus sys/pass@local db as sysdba

    create public database link APEXDB
    connect to PSEASYRUN identified by EASYRUN#123
    using '(DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.203.123.196)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SID = HR91DMO)
    )
    )'
    /

    link is created

    when i executed SELECT SYSDATE FROM DUAL@APEXDB FROM local db

    it is saying like this

    ERROR at line 1:
    ORA-01017: invalid username/password; logon denied
    ORA-02063: preceding line from APEXDB

    i hope u understand this.

    please help
  • 7. Re: Creating Database link
    952768 Journeyer
    Currently Being Moderated
    Please try to understand that we are trying step by step to see what the problem is.

    The best think to start with is to start a sqlplus session from the same server as the database which has the database link,
    to see if

    - connection to the remote database will work
    - connection with the user/password will work.

    When it is succesfull we can examine the next step.

    Problems could be:

    - remote database user is not created
    - remote database user password is different (upper case/lower case)
    - tns connection will not work with the remote database
    - etc.
  • 8. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    Able to connect to remote database as sys user and PSEASYRUN user.

    please respond earliest

    Edited by: 963852 on Oct 10, 2012 6:02 AM
  • 9. Re: Creating Database link
    952768 Journeyer
    Currently Being Moderated
    Oke connection works directly.

    Maybe you can use the tnsname (is it HR91DMO?) and use " around the password.

    Also check if there is maybe a dblink with the same name created in public.
    You can check your db links with: select * from dba_db_links;

    Create your dblink again like this:

    CREATE DATABASE LINK APEXDB
    CONNECT TO PSEASYRUN
    IDENTIFIED BY "EASYRUN#123"
    USING 'HR91DMO';

    select * from user_tables@apexdb;

    or try it as a public dblink to test

    CREATE PUBLIC DATABASE LINK APEXDB2
    CONNECT TO PSEASYRUN
    IDENTIFIED BY "EASYRUN#123"
    USING 'HR91DMO';

    select * from user_tables@apexdb2;
  • 10. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    I created db link as you said.

    SQL> SELECT SYSDATE FROM DUAL@APEXDB;

    SYSDATE
    ---------
    11-OCT-12

    when i am querying table in HR91DMO using link,it is showing like this

    SQL> select * from sysadm.ps_job@apexdb;
    select * from sysadm.ps_job@apexdb
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-02063: preceding line from APEXDB

    even when i removed sysadm, it is like this.

    I created db link by logging as sys in local db and queried. please tell me what to do?
  • 11. Re: Creating Database link
    866990 Explorer
    Currently Being Moderated
    does user PSEASYRUN in the apexdb have rights on the sysadm.ps_job table or view?

    when you're logged on as PSEASYRUN in the apexdb, can you execute that select statement succesfully?

    .R
  • 12. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    i don't know how to check whether PSEASYRUN in HR91DMO has rights or not.

    please tell. if not tell how to give rights.

    when i executed that select in HR91DMO as PSEASYRUN user,it is showing like this

    SQL> select * from sysadm.ps_job@apexdb;
    select * from sysadm.ps_job@apexdb
    *
    ERROR at line 1:
    ORA-12154: TNS:could not resolve the connect identifier specified

    other than dual,no other table is accessible from db link.

    tell me why dual is accessing,others are not accessing


    I queried v$dblink table. please check

    SQL> select * from v$dblink;

    DB_LINK
    --------------------------------------------------------------------------------

    OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH
    ---------- --- --- ------ ------------ --- --- ---------------------
    APEXDB
    0 YES NO UNKN 0 YES NO 1

    is dblink open?

    Edited by: 963852 on Oct 11, 2012 12:47 AM

    Edited by: 963852 on Oct 11, 2012 12:55 AM
  • 13. Re: Creating Database link
    866990 Explorer
    Currently Being Moderated
    First, you must not use the @apexdb when you're querying in the remote database.

    Logon to the apexdb as PSEASYRUN with sqlplus and try this query, try:
    select * from sysadm.ps_job;
    and try
    select * from user_tab_privs;
    Does this return any records? This query shows which table the user can see.
    If not, you need to get rights on the sysadm.ps_job table first.

    .Robin
  • 14. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    here apexdb is db link. I am querying with this link in local db only


    see the output

    SQL> select * from sysadm.ps_job;
    select * from sysadm.ps_job
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    SQL> select * from user_tab_privs;

    no rows selected


    what next
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points