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
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
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our Oracle gurus are waiting to answer your toughest questions.
This was first published in April 2001