This discussion is archived
7 Replies Latest reply: Jan 28, 2013 6:32 AM by Paul Horth RSS

Remote DB connection without DB link

719075 Newbie
Currently Being Moderated
Hi All,
I have a scenario as i have to connect to a remote oracle DB from my database without creating the database link my database. Is it feasible, if so how to achevie the same. I have the connection details in my databse like credentials and the service name. the tns entry is also in my database server machine
  • 1. Re: Remote DB connection without DB link
    706888 Pro
    Currently Being Moderated
    Hi,

    It depends on what you want to do with your remote connection.
    For instance if you want to copy data between database you can use the COPY command in SQL*PLUS ..

    http://download.oracle.com/docs/cd/B10501_01/server.920/a90842/apb.htm

    Sorry i didn't understood the question...
    What you want is to initiate a connection from a Server to another?
    If you want to use the same user (let'say from a Session A opened in the server A to the server B without DB links then i don't believe that's feasible that way).
    If you want to reach the other Server from a session, you can invoke a shell script in which a NEW session will be openned in the remote database ("TRANSACTIONNALY speaking" it's not the same at all, it depends on what you want to achieve).

    Edited by: user11268895 on Jul 12, 2010 10:35 AM
  • 2. Re: Remote DB connection without DB link
    sybrand_b Guru
    Currently Being Moderated
    As you cannot connect to a remote database without a database link, you don't have a scenario.

    ---------------
    Sybrand Bakker
    Senior Oracle DBA
  • 3. Re: Remote DB connection without DB link
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Ramesh_R wrote:

    I have a scenario as i have to connect to a remote oracle DB from my database without creating the database link my database.
    It is like saying you want to drink a glass of water.. but without the glass and without the water. It simply does not make sense saying that you want a network connection between database a and b, but a database link (which defines a network connection between databases a and b ) is not to be used.

    Why do you not want to use a database link?
  • 4. Re: Remote DB connection without DB link
    steve.west Newbie
    Currently Being Moderated
    I think this person is asking whether they can connect to another database "on the fly" without pre-creating the database link. A "dynamic database link" if you like. You can do this in SQLplus from the command line:

    sqlplus user/pwd@server:port/sid

    so why can you not do something similar in PL/SQL, why do you have to predefine the database link before you need it? It's actually an interesting question, anybody got any ideas?


    Steve
  • 5. Re: Remote DB connection without DB link
    577678 Newbie
    Currently Being Moderated
    please use this

    sqlplus
    'scott/tiger@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost.localdomain)(PORT=152
    1)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ora9ir2.kyte.com)))'


    or you can read from http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:45033135081903
  • 6. Re: Remote DB connection without DB link
    user11999337 Newbie
    Currently Being Moderated
    Hi All,
    I have a similar question in this regard.

    Let's say I want to run a query from a database and use data from another database. I have the user, password, SID and port number of the other database but do not want to create a separate connection using sqlplus or connect commands.

    Something like this:

    SQL> show user
    USER is "A"
    SQL> select a.col1, b.col2 from tab1 a, tab2@????? where <some condition>;

    Can this connection be done on the fly ?? I agree that a DB link will do the trick, but let's say I am not supposed to create objects on the other database.
  • 7. Re: Remote DB connection without DB link
    Paul Horth Expert
    Currently Being Moderated
    user11999337 wrote:
    Hi All,
    I have a similar question in this regard.

    Let's say I want to run a query from a database and use data from another database. I have the user, password, SID and port number of the other database but do not want to create a separate connection using sqlplus or connect commands.

    Something like this:

    SQL> show user
    USER is "A"
    SQL> select a.col1, b.col2 from tab1 a, tab2@????? where <some condition>;

    Can this connection be done on the fly ?? I agree that a DB link will do the trick, but let's say I am not supposed to create objects on the other database.
    Please do not reply to answered 3 year old threads.

    Start one of your own (referencing this one if necessary).

Legend

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