Ever wanted to perform some operation against all the objects in a schema? For example a one-time management task to turn off parallel for all tables and indexes. Another example might be an on-going task to disable and later enable triggers and referential integrity to speed up a recurring data load job. These types of tasks are not uncommon, however many people don’t know how to leverage dynamic SQL scripting to make such jobs trivially easy. So I’ll show dynamic SQL scripts for these two specific examples – which will provide you both a working knowledge and template for your own use.
-- @NOPAR.SQL schema_name
-- Turns off parallel
-- for all tables and indexes
-- for the named schema
set echo off
set verify off
set feedback off
set linesize 256
set pagesize 0
set term off
select 'alter table '||owner||'.'||table_name||' noparallel;'
where owner = upper('&1')
order by table_name;
select 'alter index '||owner||'.'||index_name||' noparallel;'
order by index_name;
set term on
A key aspect to understand is that as this SQL script runs we want all its output to be the new SQL script we’re creating to run. So we don’t want any excessive output such as echoing the commands being run, verification of parameter values (i.e. old vs. new), or feedback about the rows counts processed. Hence the first three SET commands address this. We also don’t know how long an output text line will be, so we need to set the line size accordingly. Finally we don’t want select headers or page breaks, so easiest way to get both is to set page size to zero. So now our generated script output is nothing more than just the SQL commands.
Note that I’m spooling the output to my Windows temporary directory as defined by the “%TEMP%” environment variable, plus I don’t want to see the select command output wiz by – set I set terminal output to off. Then all I do are some common select commands against Oracle data dictionary tables such as ALL_TABLES – but notice that I’m actually using text literals and concatenation operations with table columns to construct SQL commands rather than just typical select output. Once such output has been completed I simply set the terminal output back on and execute (i.e. @) the SQL script this script just created. That’s all there is to it.
-- @SETALL.SQL schema_name OFF|ON
-- Turns off triggers and referential integrity (RI)
-- for all tables
select 'alter table '||owner||'.'||table_name||decode(upper('&2'),'OFF',' disable constraint ',' enable constraint ')||constraint_name||';'
and constraint_type = 'R'
and status = decode(upper('&2'),'OFF','ENABLED','DISABLED');
select 'alter trigger '||owner||'.'||trigger_name||decode(upper('&2'),'OFF',' disable',' enable')||';'
where table_owner = upper('&1')
This script was just a little more complicated, but essentially uses the exact same technique. In fact all the SET commands are the same. The SPOOL command only differs in the name of the temporary file. Then of course the select commands to build the SQL are different. Note that I added a second input parameter that allows for me to use this one script to both turn on and off the items of interest via the DECODE function. So I only need this one script for both tasks – turning off vs. on.
There’s literally no limit to how creative you can be with such dynamic SQL scripts. For example a long time ago before Oracle provided the metadata package to regurgitate the DDL to create an object, I had a complex dynamic SQL script to reverse engineer all of my database objects. The key take away here is this technique should be on any DBA or senior database developers skills list.