Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



Rampant Oracle In-Focus Series


  Oracle Performance Troubleshooting
with Dictionary Internals SQL & Tuning Scripts

Robin Schumacher
ISBN 0-9727513-4-3 
Publication Date - May 2003
Retail Price $27.95
/  £17.95 

Order now and get 30% off the retail price!

You also receive immediate online access to the code depot!

Only $19.95

Sample Text:         

Once you identify tablespace fragmentation , what do you do about it?  Honeycomb fragmentation is easy to fix.  All that needs to be done is to combine adjacent free segments into one by issuing a coalesce  statement similar to this:

alter tablespace USERS coalesce;

Bubble fragmentation  is more difficult to handle.  Of course, the best course of action is to prevent it in the first place.  The best weapon for this is to use locally-managed tablespaces.  It may sound too simple, but in reality, implementing these storage structures in your database can just about remove the need to perform full tablespace reorganizations. 

However, what do you do if you are in a situation where you have many databases that were set up with dictionary-managed  tablespaces?  You have two options, at least if you are running Oracle 8i (the version that gave birth to locally-managed tablespaces).  First, you can create new locally-managed tablespaces in your database and then perform one last, full tablespace reorganization of all the database objects into the new tablespaces. 

Needless to say, this can be a difficult task if you have large databases and no third-party reorganization tool.  However, it will be worth the effort as you will likely never have to reorganize your tablespaces again, and your objects should never again encounter a maximum extent limit. 

If you are using Oracle 8.1.6 or higher, you can convert any current dictionary-managed  tablespaces to locally-managed tablespaces.  Buried in the Oracle documentation is a procedure for converting a tablespace's extent management from dictionary to local or vice-versa. 

The additions to the sys.dbms_space_admin  package make it quite simple to convert a tablespace to locally-managed (or to convert a locally-managed tablespace to dictionary- managed if desired).  For example, if you want to convert a dictionary-managed tablespace, called USERS to locally-managed in Oracle; all you would have to do is issue this single command:      

sys.dbms_space_admin .tablespace_migrate_to_local('USERS')

If you are afraid of how long this procedure might take on large tablespaces, do not be.  It actually runs very fast.  If, for some reason, you would like to take a tablespace that is locally-managed back to dictionary management, you can issue this command:

sys.dbms_space_admin .tablespace_migrate_from_local('USERS')

There are a few restrictions on these conversion procedures (for example, 9i UNDO tablespaces currently cannot be converted, etc.), so you should check the Oracle documentation for the specifics of using these new procedures.  Also, note that converting a dictionary-managed  tablespace that has existing objects to local will not magically rebuild all the existing object extents to conform to the sizing guidelines used by locally-managed tablespaces. 

If your situation precludes the use of locally-managed tablespaces, what choices are you left with to control tablespace fragmentation ?  One thing you can do is manually mimicking the mechanisms of locally-managed tablespaces.  This is done by:

  • Creating a tablespace that has same-sized extents for every object's INITIAL and NEXT extent values

  • Setting the tablespace's PCTINCREASE property to zero

  • Creating new objects in the tablespace without storage properties so they will inherit the tablespace's default storage properties

  • Setting each object's maximum extent limit to unlimited


If you're a DBA who's looking for real world Oracle tuning techniques, Oracle scripts, and advice on how to get to the heart of critical Oracle performance problems, then you've come to the right place. Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning Scripts was written by one the world's most widely-read DBAs and Oracle internals experts. Robin Schumacher focuses his incredible knowledge of the Oracle data dictionary into a superb book that shows how to quickly troubleshoot and correct Oracle performance problems. 

Plus! The online code depot is available immediately!





 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks