Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    21

    Unanswered: Cross-database Query?

    What's the syntax for doing this in Oracle and does it require special setup to implement?

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    It would require setting up database links then you can query across databases with "tablename@databaselinkname"


    ie;
    sql> create database link linkname connect as username identified by password using 'abc'; --- abc is the service

    sql> select count(*) from tablename@linkname.world;


    HTH
    Gregg

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Cross-database Query?

    If you want to query a different database from the one you are logged into, you will need a database link.

    Create Database Link LINKNAME
    Connect to USERID identified by PASSWORD
    Using 'INSTANCENAME';

    then you query any tables/views that are granted select privs to the above mentioned USERID

    so

    Select data
    From table_or_view_name@LINKNAME
    Where conditions;

    HIH

  4. #4
    Join Date
    Jan 2004
    Posts
    21
    That's exactly what I needed. Thanks.

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: Cross-database Query?

    To carry out a database link needs some details.

    1.- Check the domain in your sqlnet.ora file located in the server
    2.- Enter the entry to point to another database in tnsnames.ora file
    3.- Check the resolution of the service with and without the domain alias with tnsping utility
    4.- Create the database link ( It recommendable that the name of the database link be the same name of the queried database to avoid more details with global_names value )

    CREATE DATABASE LINK
    Purpose
    Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

    Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database in by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

    http://download-west.oracle.com/docs...htm#SQLRF01205
    Joel Pérez

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •