Oracle Dynamic Scripting

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.

 

Example #1: Turn off parallel for all tables and indexes for a named schema

 

-- @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

spool %TEMP%\nopar.tmp

 

select 'alter table '||owner||'.'||table_name||' noparallel;'

  from all_tables

  where owner = upper('&1')

  order by table_name;

 

select 'alter index '||owner||'.'||index_name||' noparallel;'

  from all_indexes

  where owner = upper('&1')

  order by index_name;

 

spool off

set term on

 

@%TEMP%\nopar.tmp

 

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.

 

Example #2: Disable and enable triggers and referential integrity (RI) for a named schema

 

-- @SETALL.SQL  schema_name  OFF|ON

-- Turns off triggers and referential integrity (RI)

-- for all tables

-- for the named schema

 

set echo off

set verify off

set feedback off

 

set linesize 256

set pagesize 0

 

set term off

spool %TEMP%\setall.tmp

 

select 'alter table '||owner||'.'||table_name||decode(upper('&2'),'OFF',' disable constraint ',' enable constraint ')||constraint_name||';'

  from all_constraints

  where owner = upper('&1')

  and constraint_type = 'R'

  and status = decode(upper('&2'),'OFF','ENABLED','DISABLED');

 

select 'alter trigger '||owner||'.'||trigger_name||decode(upper('&2'),'OFF',' disable',' enable')||';'

  from all_triggers

  where table_owner = upper('&1')

  and status = decode(upper('&2'),'OFF','ENABLED','DISABLED');

 

spool off

set term on

 

@%TEMP%\setall.tmp

 

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.