This discussion is archived
11 Replies Latest reply: Feb 26, 2013 4:28 AM by EdStevens RSS

Connecting to remote database without using db links

975171 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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