A Simple Tool Free Method of Creating Large DBA Command Files
By John Adolph Palinski
DBA's often have to create large command files to perform routine maintenance tasks. This article shows you a fast and simple method of creating command files using no more than a Spool command and a Select statement.
How often have you had to write long scripts to perform DBA tasks such as rebuilding tablespace indexes? DBA work is very repetitive and the need to create long command file scripts is constant. There are tools such as Ultra-Edit that have macros that repeat keystrokes that make the task easier. The macros can be used to modify hundreds of lines saving the DBA precious time. It is important to have tools that make the creation of command files easy. We all have spent time copying and pasting, so a fast method can come in handy and get you home earl. Ultra-Edit and other purchased tools are great, but may not be available at all job sites. However, the following method is tool independent and is available where ever Oracle exists.
To illustrate the concept, assume that a tablespace contains a large number of indexes. You would like to rebuild the tablespace indexes. Two Alter Index commands are needed for each index. The first Alter command uses the Rebuild and Parallel options. Parallel is used in order to employ multiple processors in the creation of the index. The second Alter command resets the Parallel option to a single processor. The following are the two statements:
Alter index a_index rebuild parallel (degree 2); -- Rebuilds the index using two processors
Alter index a_index parallel (degree 1); -- Resets the number of processors to 1
If the tablespace contains a large number of indexes, it is apparent that a lot of time can be used in developing the command file. Selecting each file for rebuild in a tool like Toad is also unthinkable. A tool free method of creating the command file is to execute a Select statement having the data dictionary object name as part of an expression and concatenate the command around the object. Spool the Select statement output to an external file. This external file is the command file and can then be executed in SQL*Plus or whatever tool you have available.
If you need to build several commands using the same object as in the above example, place the Chr(10) function and parameter value between the derived commands. Chr(10) places a new line symbol in the output file allowing the resulting spool file to be viewed vertically rather than horizontally in a text editing tool.
Listing 1 illustrates the concept. The target tablespace has approximately 560 indexes that must be rebuild. The indexes are identified using the DBA_Indexes table. The Alter Index verbage is concatenated around the DBA_indexes Index_name column. The Chr(10) is a function is added after the first Alter command. It places the second half of the expression on a new line. This makes the command file readable if it is opened in an editing tool such as Notepad or Ultra-Edit.
Listing 1 - Select statement creating the command file
set linesize 300
set head off
spool c:\index_rebuild.sql
select 'alter index '||owner||'.'||index_name||' rebuild parallel (degree 2);'||
chr(10)||
'alter index '||owner||'.'||index_name||' parallel (degree 1)'
from dba_indexes
where tablespace_name = 'DW_DIM_T_DATA';
spool off
Listing 2 shows the output of the script.
Listing 2 - Partial command file
SQL> select 'alter index '||owner||'.'||index_name||' rebuild parallel (degree 2);'||
2 chr(10)||
3 'alter index '||owner||'.'||index_name||' parallel (degree 1)'
4 from dba_indexes
5 where tablespace_name = 'DW_DIM_T_DATA';
alter index DW_DIMENSIONS_T.WO_DETAIL_TRANS_FACT_I09 rebuild parallel (degree 2);
alter index DW_DIMENSIONS_T.WO_DETAIL_TRANS_FACT_I09 rebuild parallel (degree 1);
...
571 rows selected.
SQL> spool off
As you can see, the Listing 1 Select statement easily created a script file that can be used to rebuild all of the target tablespace's indexes. The DBA need only edit the beginning and end of the file to eliminate unwanted verbage such as the Select statement. Of course, the DBA can leave the verbage since Oracle won't understand it. The file can then be executed with the '@c:\rebuild_index.sql' command. The technique of using SQL and the data dictionary to create large command files can greatly simplify a DBA's work. It also proves that large amounts of work can be done with simple free tools.
I would like to thank Mike Yakus, a very experienced DBA for this tip.
John Adolph Palinski is a former adjunct faculty member at the University of Nebraska at Omaha and Iowa Western Community College. He is the author of the "Oracle Database Construction Kit", Que, 1997, the "Oracle SQL and PL/SQL Handbook: A Guide for Data Administrators, Developers, and Business Analysts", Addison Wesley Longman, 2002, and "Oracle 9i Developer: Developing Web Applications With Forms Builder", Course Technology, 2003. He has also written numerous articles for periodicals such as Oracle Internals and Oracle Professional. He provides Oracle training and consulting services. One of his passions is to help customers set up logical business models to improve information efficiency and effectiveness. He may be contacted at www.oracle-trainer.com.
