7 Replies Latest reply: Jan 28, 2013 8:32 AM by Paul Horth RSS

    Remote DB connection without DB link

    Ramesh_R
      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
          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
            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
              Billy~Verreynne
              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
                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
                  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
                    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
                      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).