How to run a select against multiple databases

[symon_braunbaer]

Dear experts,

I need to run a select against all our databases, which are running on a separate server each.

I found this:

Looping between multiple servers for SQL*Plus

which seemed to be doing the trick, but it relies on /etc/oratab, but in our environment this file

is not in use, i.e.

/var/opt/oracle/oratab - this file has comments only and does not contain any real entries

/oracle/<SID>/11203/install/oratab - this file has 0 size

Thank you!

[ACE-SAP]

Hello

Database link is a good technique for running a query against multiple database, but you will need to statically define the DB you want to access.

Best regards

CREATE DATABASE LINK dev_link CONNECT TO system IDENTIFIED BY password

USING '(DESCRIPTION =  (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =sapdev)(PORT = 1527)) ) (CONNECT_DATA = (SID = DEV) ) )';

select * from sapsr3.REORGJOBS@dev_link;

drop database link  dev_link;

25383 - Database links, synonyms, remote DB

[symon_braunbaer]

Hello,

thanks, but it is not applicable for us, as it is not continually needed...

[ACE-SAP]

Hi

You can drop the link as soon as you do not need it anymore. I use links to run scripts against multiple DBs, creating & deleting the link at the beginning / end of the script

Regards