I am new to toad and also oracle. I am using toad and I connect to 16 different DB server and all these servers has same table structure with same name.
Sometimes I will be asked to check the data for a particular table across the servers.
Is there any way to write a query and get this done from toad?
Thanks, Nishith.
Hi nishithjain,
you can do this in Toad as follows:
In the editor, write your script, test it, and save it somewhere safe. It must be saved. If you have already got a saved script (or scripts_ that you want to run, the skip this bit.
Now goto Utilities->Script Manager. A new window will open.
There are two halves to the new window, the top and bottom. In the bottom, you will soon see some scripts, but first …
In the top half of the screen, is a list of connections that you wish to run a/some scripts against. Above this is a button bar - you’ll need that later. First click the “add” on the far right of the list of connections to add further connections. They don;t have to be open. Your current connection will be automatically added by the way - but you can remove it if necessary.
When you have added all the desired databases, click the “new data file” button in the afore mentions button bar. It’s the 4th one in, and looks like a blank sheet of paper with a “dog ear”.
First you must select a folder and then give the new file a name.
You then get to add one script in a following dialogue. Navigate to the script you want and add it as desired.
The following dialogue shows the script you added. You can add more using the ‘ADD’ button, or click the ‘OK’ button to stop adding, however, before you click the ‘OK’ button, you must enter a group name. make it descriptive, and you can, if desired, add a description too. Then click ‘OK’.
If you forgot to add some other scripts, then just above the list of scripts in the bottom half of the screen, you will see a button to add more. Have fun.
When you have added all your scripts, you can change the order in which they will run etc.
To run the scripts against the database connections listed, go back to the button bar at t he top of the connections half of the screen, and click the first button. The icon is a green arrow head with PL/SQL in text beside it.
After a short delay, the script(s) will have been run against all the databases you chose, and the results are at the bottom of the screen.
There are options to enable each script to spool out etc, feel free to examine the options button, on the top button bar above the connections window.
The connections are saved in the datafile along with the scripts, so next time you need to run the same script(s), simply click to open the data file and everything will be re-loaded for you.
HTH
Sorry, I forgot to mention:
when you come to run the scripts, you must select them in the lower window first, then press the run button. That button will only execute those scripts in the lower half, which are currently selected.
Excellet, thanks a ton… Wow, you saved my time, thanks again.
There is a blog post on this matter - I should have searched first I suppose - at https://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2017/08/25/run-oracle-scripts-even-against-multiple-databases-in-the-toad-script-manager however, it doesn’t explain how exactly you are able to add scripts. You need to create or load a data file (*.sdf) first, only then can you begin adding scripts.
Bert (Scalzo) also has a couple of blog posts on the matter from some time back, and I’m afraid that they suffer from the same omission. 
They can be found at:
Bert and Dan Hotka have a new version of the Toad For Oracle Unleashed book available from Amazon (https://www.amazon.co.uk/Toad-Oracle-Unleashed-Bert-Scalzo/dp/0134131851) - change .co.uk to suit your location, it should get you to the same book - and from other retailers. If you are new to Toad then you might find it useful. I did - and I was one of the technical reviewers when it was originally in progress. Highly recommended.
John Dorlon also has a blog post, using a different method, the Automation Designer, at https://www.toadworld.com/products/toad-for-oracle/b/weblog/archive/2016/10/26/performing-actions-in-multiple-databases-with-toad which you might also find useful.