Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Don’t’ Write Iterative SQL Statements – Generate SQL with SQL

The statement above alters every user in the database to use the userwork tablespace as their default tablespace. The spool command writes the output to another file that will be used as input to SQL*PLUS to perform the alterations. The various set commands turns the display messages off so the output file can be executed in SQL*PLUS with little or no changes. The hard coded values ‘alter user ‘ and ‘default tablespace userwork;’ will be included in every row of the result set. The pipe characters ("|"), tells SQL*PLUS to write the output values next to each other (a comma will cause SQL*PLUS to display 3 spaces).

Input Truncated to 9 Characters

Have you ever received that message in your SQL*PLUS output? Irritating, isn’t it? Especially when you are using SQL to automatically generate other SQL. Place a carriage return after the last line of the statement or procedure. The message is gone!

Conclusion

The intent of this chapter was to provide readers with a general list of hints, tips and information on administering an Oracle database environment.   A few of the topics covered bear repeating:

• Don’t take rules of thumb as gospel.  Databases are like fingerprints, they are all different and they will react differently to administrative changes.  Make the desired change and test the results.  Then develop your own rules of thumb.
   

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.