Oracle Tips   Donald K. Burleson

Oracle Tablespace Fragmentation

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 fragmentationis more difficult to handle.  Of course, the best course of action is to prevent it in the first place.  And as discussed earlier, 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:      

 books.com/book_2003_1_perf.htm

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.