Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    156

    Unanswered: query from multiple instances?

    hi,

    anyone know if it is possible for me to query an instance i am not logged into.

    It would make my life 11.11111... times easier in the mornings. instead of querying the tables from 9 instances... to check for tables and column names... i quite fancy writing a script to do that for me.

    8i winNT

    tx.
    rgs,

    Ghostman

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    use DB_LINKS

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

    Re: query from multiple instances?

    Create a database link to the other instances and then run the query against the link

    Create Database Link LINKNAME
    Connect to SCHEMA identified by SCHEMA_PASSWORD
    using 'INSTANCE'

    From the instance that holds the tables you need to query
    grant select privs to the SCHEMA you will use in the link

    Grant Select On TABLE_NAME to SCHEMA;

    From the instance that you create your multiple DB links, query through the link

    Select COLUMN_NAMES
    From TABLE_NAME@LINKNAME
    Where CONDITIONS;

    You can repeat this process for each instance.

    You can do all of this from a script, and schedule the script to run each morning...

    HIH

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You may need to check the global_name from each database and create the db_link according to the global_name (initxxxx.ora -

    global_names=true

    Gregg

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    It looks like the answer is no. :-)
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Actually the answer is YES. I have a client that where I created a view to pull data from 12 databases (with db_links).

    Gregg

Posting Permissions

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