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

More on defragmenting a tablespace to free space

Did I miss something? The question below regards tablespace free space. You state that prior to 8i, free space is dictionary-managed. I have an 8.0.5 instance, and I manage the tablespace free space. Often extents are created and the tablespace becomes fragmented. Creating and dropping indexes also fragements the tablespace. I use the command:

 ALTER TABLESPACE <tablespace name> 
This does not always pull the free space together, but most of the time it does. When it does not I may have to export all objects in a tablespace, coalesce the free space, then import the objects back into the tablespace. This works on pre 8i instances.

The question posed to you by another reader:

I have a tablespace consisting of four data files. Each data file is 1 GB. Overall, the tablespace has around 1.5 GB of data free. But the maximum contiguous free space available is only 50 MB. Can I defragment a tablespace to solve this issue? If yes what is the command?
This question posed on 07 February 2003

I'm a little unclear what your question is. You haven't missed anything except for the newer features of Oracle that make items like the ALTER TABLESPACE COALESCE command obsolete for today's Oracle DBA. This command will only defragment contiguous chunks of free space. It will not move any objects around, and it will not defragment objects. Additionally, if the default value for PCTINCREASE is a non-zero value for this tablespace, SMON will automatically coalesce free space for that tablespace, in effect issuing the above command for you.

If you read my answer again, and using your own comments, you will notice that "This does not always pull the free space together but, most of the time it does." Your solution was to export/import the objects to fix this problem. My solution is to use Oracle's new Locally Managed Tablespaces with uniform extents sizes. This way, you don't have to periodically issue the ALTER TABLESPACE COALESCE command, and you don't have to worry about ever having chunks of free space too small to use. Granted, my solution isn't easier in the short term, but I believe it to be a better solution in the long term.

For More Information

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.