Sql Query [message #661567] |
Fri, 24 March 2017 01:54 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/6d24ca5b96de8a2dc7e13671a289e9c6?s=64&d=mm&r=g) |
rohitdba
Messages: 14 Registered: March 2017
|
Junior Member |
|
|
How to run a select query on multiple server simultaneously .
|
|
|
|
|
Re: Sql Query [message #661570 is a reply to message #661569] |
Fri, 24 March 2017 02:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Er.... that is paradoxical! Do you WANT to run it on each server, or do you NOT want to run it on each server?
|
|
|
|
Re: Sql Query [message #661572 is a reply to message #661571] |
Fri, 24 March 2017 02:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
John Watson
Messages: 8932 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Perhaps you could create thirty database links and run it through them. Or you could use the Scheduler to run it as a remote database job. It seem a lot of trouble to go to, though.
|
|
|
Re: Sql Query [message #661573 is a reply to message #661572] |
Fri, 24 March 2017 02:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21809 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's how I do it.
MY_SCRIPT.SQL is located in C:\temp directory. It looks like this:
connect &2
-- If you want to spool output into log files:
spool &1
-- If you want to see commands in log files:
set echo on
-- Commands to be executed
begin
my_package.my_procedure;
end;
/
spool off
exit
Furthermore, I have a batch (.bat) script which looks like this:
@echo on
set SCRIPT=C:\temp\my_script.sql
if not exist %SCRIPT% goto END
start sqlplus.exe /nolog @%SCRIPT% %SCRIPT%.server1.log "SERVER1_USERNAME"/"SERVER1_PASSWORD"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=x.x.x.x)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ser1)))
start sqlplus.exe /nolog @%SCRIPT% %SCRIPT%.server2.log "SERVER2_USERNAME"/"SERVER2_PASSWORD"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=y.y.y.y)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ser2)))
start sqlplus.exe /nolog @%SCRIPT% %SCRIPT%.server3.log "SERVER3_USERNAME"/"SERVER3_PASSWORD"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=z.z.z.z)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ser3)))
start sqlplus.exe /nolog @%SCRIPT% %SCRIPT%.server4.log "SERVER4_USERNAME"/"SERVER4_PASSWORD"@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=w.w.w.w)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ser4)))
:END
Double click on the .bat script runs MY_SCRIPT.SQL at once on all "servers" (of course, these don't have to be different servers, could be different users in the same database, but that's the general idea).
[EDIT] Credits go to my colleague, S.K. (he'll recognize himself if he ever sees this)
[Updated on: Fri, 24 March 2017 02:23] Report message to a moderator
|
|
|
|
|
|
|
|