11 Replies Latest reply: Feb 26, 2013 6:28 AM by EdStevens RSS

    Connecting to remote database without using db links

    975171
      Referred Thread:

      Re: Remote DB connection without DB link


      In reference to the thread above, my question goes like this:

      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.
        • 1. Re: Connecting to remote database without using db links
          JohnWatson
          You mean like this?
          orcl> host tnsping orcl
          
          TNS Ping Utility for 32-bit Windows: Version 11.2.0.3.0 - Production on 29-JAN-2013 08:46:04
          
          Copyright (c) 1997, 2011, Oracle.  All rights reserved.
          
          Used parameter files:
          c:\app\john\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
          
          
          Used TNSNAMES adapter to resolve the alias
          Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = jwdell.bplc.co.za)(P
          OK (10 msec)
          
          orcl> select * from dual@orcl;
          
          D
          -
          X
          
          orcl> 
          • 2. Re: Connecting to remote database without using db links
            Billy~Verreynne
            A database link is a local object. So a link to a remote database will exist as an object in the user A schema on the local database.

            However, the user A schema needs the privileged "+create database link+" to create such an object in its schema.
            • 3. Re: Connecting to remote database without using db links
              CoBy
              Hello,

              You do not have to create the dblink in the remote database, you can create it in your own and then query:

              i.E.
              CREATE DATABASE LINK remotelink connect to username identified by password using 'identifier';

              Select * from table@remotelink

              I don't know any other possibilities for connecting to a remote database from an existing session.

              Best regards,
              CoBy
              • 4. Re: Connecting to remote database without using db links
                Hemant K Chitale
                And the person creating the DBLink needs to be given an account on the remote database which has the required (e.g. SELECT) privilege on the target tables (in the remote database). Generally, I recommend creating a separate account with minimal privileges in the remote database. The DBA of the remote database has to setup and provide this account. (Unless the same username/password exists in both databases with the right privileges !)


                Hemant K Chitale
                • 5. Re: Connecting to remote database without using db links
                  moreajays
                  Hi,

                  Setting TWO_TASK in your osuser profile can help you to connect directly to remote database from client without using sqlplus <username>@<TNS_Alias>/<pwd>
                  Make sure tnsping <TBS_Alias> it resolvable
                  TWO_TASK=REMCORP
                  export TWO_TASK
                  Thanks,
                  Ajay More
                  http://www.moreajays.com
                  • 6. Re: Connecting to remote database without using db links
                    Billy~Verreynne
                    moreajays wrote:

                    Setting TWO_TASK in your osuser profile can help you to connect directly to remote database from client without using sqlplus <username>@<TNS_Alias>/<pwd>
                    Keep in mind that sqlplus still uses the same method to connect to the remote database. TWO_TASK changes client driver behaviour. It does not change how that connectivity works over TCP - that remains the same.
                    • 7. Re: Connecting to remote database without using db links
                      EdStevens
                      CoBy wrote:
                      Hello,

                      You do not have to create the dblink in the remote database, you can create it in your own and then query:

                      i.E.
                      CREATE DATABASE LINK remotelink connect to username identified by password using 'identifier';

                      Select * from table@remotelink

                      I don't know any other possibilities for connecting to a remote database from an existing session.

                      Best regards,
                      CoBy
                      "You do not have to create the dblink in the remote database, you can create it in your own and then query:"


                      Correction/clarification:


                      "You do not --have to-- create the dblink in the remote database, you --can-- create it in your own and then query:"
                      • 8. Re: Connecting to remote database without using db links
                        975171
                        No Sir, I want to connect to the remote database without creating objects in the other and also being inside the session of the current database.
                        • 9. Re: Connecting to remote database without using db links
                          975171
                          Sir, this would help me in case I am outside the DB session, but not already inside a session of the local database.
                          All I want to do is to connect to the remote object. I do not have privileges to create objects in either my current session or the remote session. But I do have the select privilege on both.
                          • 10. Re: Connecting to remote database without using db links
                            Billy~Verreynne
                            You need two sets of privs on the remote database to view data in that database schema:
                            - create session (the schema must allow client connections to create schema sessions)
                            - select on other schema tables whose contents to view

                            You need a single priv on the local database, to connect to that remote schema on the other database:
                            - create database link

                            You also need the following in order to create the db link:
                            - the remote schema name and password
                            - IP address or hostname of the remote db server
                            - the remote Listener's tcp endpoint port
                            - the name of the remote database (SID, instance or service name)

                            If you do not have these privs, you cannot use/access the remote database from the local database session via a database link.

                            Other alternative methods needs additional privileges and objects on both the remote and local database sides - and are more complex (e.g. using web services, using web-enabled procedures, etc).
                            • 11. Re: Connecting to remote database without using db links
                              EdStevens
                              972168 wrote:
                              Sir, this would help me in case I am outside the DB session, but not already inside a session of the local database.
                              This makes no sense at all. You cannot do anything to or with a database when you are "outside the DB session".
                              All I want to do is to connect to the remote object. I do not have privileges to create objects in either my current session or the remote session. But I do have the select privilege on both.
                              If you are connected to database "A" and want to query an object in database "B", you MUST have a database link defined in database "A". That's how oracle works. Period. End of Story. So why is it you cannot create a db_link, or have one created for you?

                              Edited by: EdStevens on Feb 26, 2013 6:28 AM