Last week’s blog was the third in a series about how to make Toad run scripts and/or commands against multiple databases at once. Lots of people did not know that there are multiple ways in Toad to do this. This week I will highlight the fourth and final (???) method of running scripts against multiple databases using the new Toad
“App Designer” (the modern and very robust replacement for the old command line interface for running Toad actions). The new
“App Designer” makes almost anything doable in Toad able to be fully automated, scripted, scheduled, etc. I cannot in one short blog elaborate on the near limitless capabilities this new feature makes possible, so for now I’ll just focus on the very simple task of running a script against several databases at once – the mantra of this particular blog series.
Here’s a table space report I like to run regularly. While I might want to schedule this report to run against all my databases and email me the output (which the App Designer can do), for now I’ll just show the basic steps required to run the script against my collection of databases manually. Here’s a screen snapshot of the App Designer.
The steps to perform are as follows:
- Press the Create New App toolbar icon – give the application a name
- Press the Execute Script toolbar icon – fill in the screen presented
- Name the script
- Define the script file name for output criteria
Did you note the right-hand-mouse menu option for variables? That presents a pop-up window with a collection of useful predefined substitution variables. So in this example case, an execution will create a file names TableSpace_Report_SID_DATE.txt – where SID = the database connection ID and DATE = the system date with special characters stripped out so it can be used in a file name. That’s pretty much it. The Application Designer will create a text file with the application name – and it contains code that looks like the following. But don’t sweat – you don’t need to worry about this content, it’s merely the meta-data information necessary to drive Toad.
object TarActionSet
Enabled = True
ID = 1
ParentID = -1
UserName = 'Tablespace_Report'
Categories.Strings = (
'Default')
Opened = False
RunData = {
545046300A54617252756E44617461025F310B416374696F6E536574494402FF
02494402FF084661696C4D6F6465070A666D436F6E74696E7565065374617475
7307096173537563636573730000}
ExecutableItems = {
545046301254617245786563757461626C654974656D7300054974656D730ADF
0300005450463010546172457865637574655363726970740007456E61626C65
6409024944020208506172656E744944020108557365724E616D65060F457865
6375746520536372697074310B416374696F6E536574494402000B4C61737452
756E44617465050068C2F78359FE9A0E400C536372697074536F757263650707
737346696C657306546172676574070F7474446973636172644F757470757407
52756E446174610A45010000545046300A54617252756E44617461000A416374
696F6E4E616D65060F4578656375746520536372697074310B416374696F6E53
6574494402010D416374696F6E5365744E616D6506115461626C657370616365
5F5265706F72740249440202084661696C4D6F6465070A666D436F6E74696E75
650752756E44617465050068C2F78359FE9A0E400752756E5573657206074253
43414C5A4F104D657373616765732E537472696E6773010640382F31382F3230
303820383A32333A333120414D3A202045786563757465205363726970743120
2845786563757465205363726970742920737461727465642E0641382F31382F
3230303820383A32333A333120414D3A20204578656375746520536372697074
3120284578656375746520536372697074292066696E69736865642E00065374
6174757307096173537563636573730000064C6F67696E730A67010000545046
300A544C6F67696E5265637300054974656D730A4A0100005450463009544C6F
67696E5265630011456E6372797074656450617373776F7264067731372C302C
302C302C3232322C34352C3233342C3233322C36342C3230312C3132312C3738
2C35352C32312C392C3234332C33392C3234342C35392C34392C3131312C352C
3137362C37352C3231382C3133332C36372C3137392C3232372C3133372C3135
302C35352C3235322C35332C32342C3231340455736572060442455254065365
7276657206054F52444231064E756D62657202000B4C617374436F6E6E656374
05006007804D58FE9A0E4009436F6E6E656374417306064E4F524D414C05436F
6C6F7204008000000A4F7261636C65486F6D65061D433A5C4F7261636C655C70
726F647563745C31312E312E305C64625F310B4175746F436F6E6E656374080C
5361766550617373776F726408084661766F7269746508064D6574686F640200
000000001145786563757461626C65536372697074730A65000000545046300C
5461724469736B4974656D7300054974656D730A46000000545046300B546172
4469736B4974656D00044E616D650616433A5C42535C426572745C53514C5C74
73702E73716C0A45786563757461626C6509084973466F6C6465720800000000
00000000}
end
So here’s my Windows Tablespace_Report.bat file which I can double click on to run or schedule:
call "c:\program files (x86)\quest software\toad 9.7\toad.exe" bert/bert@ordb1 -a tablespace_report
call "c:\program files (x86)\quest software\toad 9.7\toad.exe" bert/bert@ordb2 -a tablespace_report
call "c:\program files (x86)\quest software\toad 9.7\toad.exe" bert/bert@ordb3 -a tablespace_report
That’s all there is to it. But please remember, the new App Designer can permit one to do nearly limitless cool things – so experiment away and enjoy
