Tip

Dynamically creating DBA scripts

This is my first tip for this site, so I thought that I would start off with a beginner level tip. Most DBAs (or developers) who have been working with Oracle for a short while will already know this tip. But this is one of the first tips that I learned and one that has saved me tons of time over the years.

Often times in my DBA duties, I have to perform some repetitive operation that would be greatly facilitated if I could use a script. But it can be a pain to manually generate this script. Luckily for me, the information I need is in the data dictionary. I just need to pull the data out of the data dictionary in such a way that I can dynamically generate my script. After my script is generated, I just run the script.

For example, I often have to move a bunch of indexes from one tablespace to another. Let's assume that all of my indexes belong to one schema owner. I can query the data dictionary to find the index names. For example:

SQL> select index_name from dba_indexes where owner='TIMEKEEP';

INDEX_NAME
------------------------------
EMPLOYEE_NAME_UQ
EMPLOYEE_PK
EMP_PROJ_PK
PARAM_NAME_IDX
PROJECT_NAME_IDX
PROJECT_PK
TIMESHEET_NO_DATE_IDX
TIMESHEET_PK

8 rows selected.

Here you can see all of the indexes that matched my criteria, i.e. owned by TIMEKEEP. I wish to move these indexes to the TIMEKEEP_IDX tablespace. How can I generate a script to move all of these for me? Simply by changing my SQL

    Requires Free Membership to View

statement slightly and spooling the output to a file. For example:

SQL> set heading off
SQL> set feedback off
SQL> spool move_idx.sql
SQL> select 'ALTER INDEX ' || owner || '.' || index_name
  2     || ' REBUILD TABLESPACE timekeep_idx;'
  3  from dba_indexes where owner='TIMEKEEP';

ALTER INDEX TIMEKEEP.EMPLOYEE_NAME_UQ REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.EMPLOYEE_PK REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.EMP_PROJ_PK REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.PARAM_NAME_IDX REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.PROJECT_NAME_IDX REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.PROJECT_PK REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.TIMESHEET_NO_DATE_IDX REBUILD TABLESPACE timekeep_idx;
ALTER INDEX TIMEKEEP.TIMESHEET_PK REBUILD TABLESPACE timekeep_idx;
SQL> spool off

The key to this is to use the string concatenation operator '||' to join the static part of my output, i.e. 'ALTER INDEX', with the dynamic part of my output, i.e. index_name (which is queried from the data dictionary).

I turned off the headings and feedback before I executed the query. I also spool the output to a file (move_idx.sql). When the query completes, I stop spooling. To move the indexes, I simply run the generated query. Often times, I want to make a few manual modifications before I run this script. Simply use your text editor to make your modifications, save the results, and run the script.

I use this technique constantly. Before any database reorganization, I dynamically generate any scripts that I will need so that they are ready to go. Reverse engineering scripts are often generated with this technique. Another thing that comes up often is that I want to perform two commands for an object. For instance, after moving the index, let's suppose that I wanted to compute statistics on the index. This can be accomplished by concatenating a character return in our string, chr(10). For example:

SQL> select 'ALTER INDEX ' || owner || '.' || index_name
  2  || ' REBUILD TABLESPACE timekeep_idx;' || chr(10)
  3  || 'ALTER INDEX ' || owner || '.' || index_name
  4  || ' COMPUTE STATISTICS;'
  5  from dba_indexes where owner='TIMEKEEP';

The 'chr(10)' will cause a linefeed and the two commands are now separated onto two lines!

Using these techniques, you are only limited by your imagination on what sort of dynamic scripts you can generate. This should make your DBA life a little easier!

About the Author

Brian Peasland has been in the IT field for 14 years working as a Computer Operator, Operations Analyst, Systems Administrator, Application Developer, and finally a Database Administrator. He holds a B.S. in Computer Science and a M.S. in Computer Science specializing in Database Systems. Additionally Mr. Peasland holds OCP DBA credentials for Oracle 7.3, 8, and 8i. He currently works for Raytheon as a database administrator.

For More Information


This was first published in April 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.