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> COALESCE;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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.