ABSTRACT
The Scenario This is a large application database with about 650 tables. Before reorganizing, the data tablespace was 160 GB and the index tablespace was 140 GB. All of the application data was on a single tablespace and all the indexes were on another tablespace. The tablespaces were fragmented a lot, and although there were about 20 GB free in each tablespace, the “unable to extent” error message was common. The fragmentation may have been caused for many reasons. The most obvious ones are:
Production support developers had privilege on application data tablespace to create their temporary tables. Creating and dropping tables left a lot of space fragmented.
