1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ADHOC query to retrieve data from multiple dbs

Discussion in 'SQL PL/SQL' started by stev1ef, Feb 5, 2009.

  1. stev1ef

    stev1ef Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi Guys,

    Completely new to Oracle and need a bit of help.

    I've got a master db on it's own server. This server is linked to 15 others - all with their own custoer specific db on. All dbs on all servers have the same structure.

    I need to write a query that will query every one of the 15 customer dbs to verify a conditon. i.e.

    select result1 from table1 where condition1 = x and condition2 = y

    Is there a kind of adhoc query I can run that will query out to all 15 customer dbs.

    Any help would be fantastic.

    Cheers
    Steve
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Have you tried db links ?
     
  3. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    DB_Links is something by which you can connect to and query other databases over a network. for example if i want to connect to a database whose SID is DEV and is located on IP 128.0.21.34, and my username/password on DEV is SCOTT/TIGER then I need to write the following query:

    Code (Text):

    create database link "TEST_DBLINK"
    connect to SCOTT
    identified by "TIGER"
    using '128.0.21.34:1524/DEV'
    i can now query a table called testing in DEV by the following query:

    Code (Text):
    select * from testing@TEST_DBLINK;
    DBLinks can be public, private etc.

    Hope this helps
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Also Another solution will be to create server level script (like shell script) to do the same task instead of creating 15 database links. Here you can swith through different DBs by using username/pwd@servicename.
     
  5. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Do yo think that it would more painful to create 15 db links instead of querying 15 database separately and then joining and presenting all the data in the tabular form ?
     
  6. stev1ef

    stev1ef Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Would probably like to create 15 different links, as if I can do this then run the query once, I will always have the link script if I need to run other types of similar query.

    Well I'm off to buy a pl/sql handbook to learn how to do all of this - does anybody have any recommendations for absolute beginers?
     
  7. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    well if you want to learn just basic sql pl/sql the w3schools tutorials are very good for absolute beginners.
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No .. its not a painful Thing .. But in practical scenario , Most of user wont have privilege to create db link . That the reason I suggested server level process (though it needs the server access) .. After all its just another suggestion