Execute SQL query on remoted server

[ranjit]

Is it possible to execute a SQL query across multiple servers remotely? I'd like to issue the below on multiple remote severs:

/usr/bin/su - oracle
export ORACLE_HOME=/u01/oracle/product/9.2.0.5
export ORACLE_SID=${sid}
/u01/oracle/product/9.2.0.5/bin/sqlplus ' /nolog'
connect / as sysdba
select dbtimezone from dual;
exit

 

[hoinz]

Yes, it can be done.
Your problems may be split into 2 or 3 questions:

Q1 How can I interact with Oracle from Unix?
A1 Have a look at this forum's FAQ section.

Q2 How can I execute commands on a remote server?
A2 Read about rsh or ssh.

Q3 And the same for more than one remote server?
A3 I remember having seen this question repeatedly being asked at Tek-tips. The search function should give you an idea.

hope this helps

 

[DrD123]

Hi ranjit,

Does the server you are running the query "from" have Oracle (at least the client) installed?

If the above is true, then just create a connect string in your tnsnames.ora file to those other servers, and run that query like this:

sqlplus username/password@connect_string @file_with_query.

It can also be put into a KSH script if you like to kick them all off, and spool the output from each SQL to it's own file.

But really the answer to the first question will decide it. If you don't have Oracle on the initial server, you'll need to use OS tools as hoinz described.

Good Luck
DrD

 

[ranjit]

Thanks for the post.

I did eventually resolve the issues using dsh and placing the SQL query in an executable script called by dsh.

Thanks