Last week’s blog was the second in a series about how to make Toad run scripts 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’ll highlight the third method of running scripts against multiple databases using the
“Toad Editor” and Dynamic SQL Scripting. This week’s method is neither the easiest nor the best, it’s just yet another way to accomplish this task. It also has the benefit of building on one of my earlier blogs about
Dynamic SQL Scripting via Toad. Furthermore, it starts us thinking about the problem in terms of its atomic operations – which will nicely lead us into next week’s fourth method (i.e. Toad Actions and Application Designer – the replacement for the older Toad Command Line facility). So please read on.
I have the Dynamic SQL script (shown here) which I use to coalesce all the tablespaces in the current database. If I load this script into the Toad Editor and press F5 (Execute Script), it will coalesce all the tablespaces in that session’s database – and only that database. There’s nothing too fancy here.
set term off
set pagesize 0
set verify off
set feedback off
spool coalesce.tmp
select 'alter tablespace '||tablespace_name||' coalesce;'
from dba_tablespaces
where status = 'ONLINE'
and contents not in ('TEMPORARY','UNDO');
spool off
set term on
set echo on
@coalesce.tmp
Now what if I want to run this script against multiple databases? Well, I can simply use the exact same technique (Dynamic SQL) that now calls another Dynamic SQL script to get Toad to run the commands against multiple databases. Look at the script below in Toad’s Editor and the resulting output – mission accomplished! So just what did I do here? I merely wrote a Dynamic SQL script that calls the
coalesce.sql script for the hard-coded databases. And yes, I know using SYSTEM is a bad practice, as is hard coding the user id and password. But I wanted to show this first. A slightly better method will be shown later.
The slightly better (i.e. more secure) solution is slightly more complex – but also happens to be much easier to dynamically make work for any number of databases without making any code modifications.
set term off
set pagesize 0
set verify off
set feedback off
spool c:\temp\coalesce_all.tmp
select 'spool c:\temp\coalesce_all.log' from dual;
select 'connect ‘||SYS_UID||’/’||SYS_PWD||'@’||DB_SID||’
'||'@coalesce.sql'
From berts_system_user_id_table;
select 'spool off' from dual;
spool off
@c:\temp\coalesce_all.tmp
Remember – in the weeks to come I’m going to demonstrate other ways to execute SQL scripts against multiple databases at once, so please continue to tune in
