Home » RDBMS Server » Server Administration » Sql Query (11g on linux server)
Sql Query [message #661567] Fri, 24 March 2017 01:54 Go to next message
rohitdba
Messages: 14
Registered: March 2017
Junior Member
How to run a select query on multiple server simultaneously .

Re: Sql Query [message #661568 is a reply to message #661567] Fri, 24 March 2017 02:04 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

I don't understand what you mean, can you describe in more detail?
Re: Sql Query [message #661569 is a reply to message #661568] Fri, 24 March 2017 02:09 Go to previous messageGo to next message
rohitdba
Messages: 14
Registered: March 2017
Junior Member
I want to run simple select query on 30 server in on go i dont want to run the query on individual servers so is there any way to do it
Re: Sql Query [message #661570 is a reply to message #661569] Fri, 24 March 2017 02:11 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #661571 is a reply to message #661570] Fri, 24 March 2017 02:14 Go to previous messageGo to next message
rohitdba
Messages: 14
Registered: March 2017
Junior Member
HI John,

Sorry for the confusion i want to run it on each server but i dont want to write a query on each server again nd again so can we do it like writing the query on one server and it will run in all 30 servers.
Re: Sql Query [message #661572 is a reply to message #661571] Fri, 24 March 2017 02:18 Go to previous messageGo to next message
John Watson
Messages: 8922
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 messageGo to next message
Littlefoot
Messages: 21806
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

Re: Sql Query [message #661574 is a reply to message #661572] Fri, 24 March 2017 02:22 Go to previous messageGo to next message
rohitdba
Messages: 14
Registered: March 2017
Junior Member
ok and how to install the oracle on 30 linux server in one go ? I dont want to go to each server and do the installation process
Re: Sql Query [message #661575 is a reply to message #661574] Fri, 24 March 2017 02:24 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
THat is a completely different question. Personally, I would just script a silent install. But if you want to be clever, you can use the Rapid Home Provisioning facility,
http://www.oracle.com/technetwork/database/database-cloud/private/rapid-home-provisioning-2405191.pdf
Re: Sql Query [message #661576 is a reply to message #661575] Fri, 24 March 2017 02:29 Go to previous messageGo to next message
rohitdba
Messages: 14
Registered: March 2017
Junior Member
John

How to go for the silent installation any links for the installation of oracle on multiple servers

Re: Sql Query [message #661577 is a reply to message #661576] Fri, 24 March 2017 02:31 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
It's all in the docs, you know. Just look up "silent mode" in the index,
http://docs.oracle.com/database/122/LADBI/toc.htm
Re: Sql Query [message #661578 is a reply to message #661577] Fri, 24 March 2017 02:39 Go to previous message
rohitdba
Messages: 14
Registered: March 2017
Junior Member
Thanks a lot John
Previous Topic: ORACLE DCN TCP Ports
Next Topic: Lock Object
Goto Forum:
  


Current Time: Thu Mar 28 08:16:53 CDT 2024