Problem solve Get help with specific problems with your technologies, process and projects.

Rebuild a table to reduce number of extents

I have a table (5 GB) which contains a long column. I've tried to rebuild the table to get rid on the extensive extents (976) with Toad, but have been unsuccessful, because of the Long. I guess the only way of rebuilding this table is through export/import approach. My question is estimating the needed size.
Rebuilding a table to reduce the number of extents is a completely unnecessary operation for an Oracle database. It is a myth that reducing the segments extents leads to better performance. There is no performance gains to be obtained from defragmenting your Oracle database. I've got tables with over 2,000 extents and I don't have performance problems. So you don't have to rebuild your table if that is your reason. Otherwise, since the table has a LONG column, the only way to do any maintenance like this is with Oracle's export/import utilities. You could write your own utilities in a language such as Perl, but that is more work than its worth.

Dig Deeper on Oracle database design and architecture