以下是最近在OTN数据库论坛上提出的一个问题：
我想重组一些表以释放部分空间，我该如何找到需要重组的表呢？我发现每当我重新组织表时，它不会释放空间，而是增加了表的大小。我们能找到通过重组获得空间的表吗？
我不打算在这里讨论“如何找到表”的问题，但我想通过引用约8年前我在《Oracle8i实践》中写的东西，来描述重新组织表的问题有多难。
想象一下，您有某种订单处理系统，其中每个订单都经过三个主要阶段-“新建”、“进行中”、“完成”。“新建”行需要50字节的存储空间，“进行中”行需要100字节，而“完成”行需要完整的150个字节。有经验的DBA很快就得出结论：PCTFREE应该设置为65%，以便在不迁移的情况下允许新行增长到完整大小（这也意味着将PCTUSED设置为35%或更低，因为PCTUSED和PCTFREE的和必须不超过100）。
每个月，至少会将12个月前完成的大多数订单从系统中删除，但有些订单由于各种原因而被保留。在一段时间内，由于这种不完全清除，表变得有点杂乱，因此DBA决定是时候重建表并抢回一些空间和效率了。
问你自己这个问题：当表被重建时，PCTFREE应该设为何值（哪怕是暂时性）？
假设表中95%的行已完成，4%正在进行，1%是新的。对于已完成的行，PCTFREE的最佳值为零，因为大量数据将永远不会再更新，但这对于最近5%的数据来说是灾难性的，因为这些数据需要足够的增长空间。相反，如果将PCTFREE设置为65%以满足新数据的需要，或者甚至设置为35%以在新数据和“进行中”数据之间进行折衷，则又会在只包含已完成数据的块中浪费大量空间。
当然，你可以让你的电子表格程序运行起来，并决定2%作为一个合理的加权平均值（95%*0+4%*35+1%*65）/100，并希望最好。你现在可以开始进行部分卸载和部分重新加载数据的复杂过程，并按你的决定改变PCTFREE。There are several (tedious) variations of making the best of an awkward situation. （此处不知道如何翻译为好）
书中接着描述了如何糊弄alter table命令来最小化records_per_block选项，以便准备一个空表，让它只允许您向每个块中插入“正确”的行数。
顺便说一下，对原始问题的一个回答指向了OraFaq上的一个条目，该条目建议您通过查看dba_tables，将avg_row_len乘以num_rows，除以块大小，然后与块数进行比较，来确定在哪些表上重建可能会有一些好处。如果块的数量比数据所需的空间大得多（比如说50%），那么这个表可能是一个值得考虑重建的表。
尽管它没有考虑到行和块的开销，但作为指向故障点的指引，它在大多数情况下，可能是相当合理的–前提是您的统计数据相当准确，并且您没有long、LOB、varray、嵌套表或UDT（用户定义类型）列–所有这些都可能会使算法有点失真。
以下附原文链接和内容：
https://jonathanlewis.wordpress.com/2007/11/23/table-rebuilds/
Here’s a question that came up on the OTN database forum recently:
I want to reorganize some tables to release some space, how can I find the tables that need to be reorganized. I found something that whenever I reorganize the tables it won’t release space, instead the size of the table increases. Can we find the tables which will gain space by reorganization ?
I’m not going to address the ‘how do we find the tables’ question here – but I thought I’d just describe how difficult the problem of re-organising tables can be by quoting something I wrote in Practical Oracle 8i nearly eight years ago.
Imagine you have some sort of order processing system where each order goes through three main stages – ‘new’, ‘in progress’, ‘completed’. A ‘new’ row requires 50 bytes of storage, an ‘in progress’ row takes up 100 bytes, and a ‘completed’ row reaches it full maturity at 150 bytes. The canny DBA quickly works out that PCTFREE should be set to 65% to allow new rows to grow to full size without migrating (which also means setting PCTUSED to 35% or less as the total of PCTUSED and PCTFREE has to be no more than 100).
Every month, most orders that were completed at least 12 months earlier are deleted from the system, but some are retained for various reasons. Over a period of time the table becomes a little ragged because of this incomplete clear down, so the DBA decides the time has come to rebuild the table and claw back some space and efficiency.
Ask yourselves this question: what should the value of PCTFREE be (at least temporarily) as the table is being rebuilt?
Assume 95% of the rows in the table are complete, 4% are in progress, and 1% are new. For the benefit of the completed rows, the best value for PCTFREE would be zero since the bulk of the data will never be updated again, but this would be disastrous for the more recent 5% of the data which needs plenty of room to grow. Conversely if you set PCTFREE to 65% to cater for the new data, or even 35% to compromise between the new data and the ‘in progress’ data, you waste a huge amount of space in the blocks which contain only completed data.
Of course you could get your spreadsheet going and decide on 2% as a reasonable weighted average – (95% * 0 + 4% * 35 + 1% * 65)/100, and hope for the best. You could start playing complicated games with partial unloads and reloads, changing PCTFREE as you go. There are several (tedious) variations of making the best of an awkward situation.
The book then goes on to describe how you might mess about with the minimize records_per_block option of the alter table command to prepare an empty table which would only allow you to insert the “correct” number of rows per block.
In passing, one of the answers to the original question pointed to an item on OraFaq that suggested that you identify tables where rebuilding might be of some benefit by looking at dba_tables, multiplying avg_row_len by num_rows, dividing by the block size then comparing with blocks. If blocks is much larger – say 50% – than the space needed for the data then you may have a table worth thinking about.
Although it made no allowances for row and block overheads as a pointer to trouble-spots it’s probably quite reasonable in most cases – provided your statistics are reasonably accurate, and you don’t have long, LOB, varray, nested table or UDT (user-defined type) columns – all of which may mess up the arithmetic a bit.
