Oracle 10 g - 无法释放表空间中的空间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122153/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle 10 g - Unable to free up space in tablespace
提问by b.roth
The tablespace in Oracle 10g is almost 100% used.
Oracle 10g 中的表空间几乎 100% 使用。
Size (MB) = 571,768.0
Used (MB) = 571,534.0
大小 (MB) = 571,768.0 已
使用 (MB) = 571,534.0
I just deleted (and committed) thousands of records in a table that belongs to a schema associated with that tablespace. Surprisingly, no space was freed up according to the Tablespaces page on Enterprise Manager.
我刚刚删除（并提交）了属于与该表空间关联的模式的表中的数千条记录。令人惊讶的是，根据企业管理器上的表空间页面，没有释放任何空间。
Question: is there anything that I need to do to force Oracle to release the space corresponding to the deleted records?
问题：我需要做些什么来强制Oracle释放与已删除记录对应的空间？
回答by FerranB
The space you are seeing is for SEGMENTS on tablespace.
您看到的空间用于表空间上的 SEGMENTS。
A Tablespace contains Segments. Each Segment is associated to an Index or a Table. Segments contains extends, and extends contains blocks. An for a table, a block contains rows.
表空间包含段。每个 Segment 都与一个索引或一个表相关联。段包含扩展，扩展包含块。对于表，块包含行。
When you delete rows on a table you are freeing the space ocuppied by rows but the space for new segments remains equal.
当您删除表上的行时，您正在释放行占用的空间，但新段的空间保持不变。
To free this space you can try to:
要释放此空间，您可以尝试：
ALTER TABLESPACE xxx COALESCE;
or
或者
ALTER TABLE xxx MOVE;
The first one, will "combine all contiguous free extents into larger contiguous extents". Depending on your configuracion, this can be executed by Oracle automatically. Also, may be it does not free to much because the location of data relative to the highwatermark on segment.
第一个，将“将所有连续的空闲盘区组合成更大的连续盘区”。根据您的配置，这可以由 Oracle 自动执行。此外，可能因为数据的位置相对于段上的高水位线而没有释放太多。
The second one "lets you relocate data of a nonpartitioned table or of a partition of a partitioned table into a new segment, optionally in a different tablespace, and optionally modify any of its storage attributes.". Be carefull with this because you need free space to achieve this, execute it against another tablespace or add more datafiles.
第二个“允许您将非分区表或分区表的分区的数据重定位到新段中，可选地在不同的表空间中，并可选地修改其任何存储属性。” . 小心这一点，因为您需要可用空间来实现这一点，对另一个表空间执行它或添加更多数据文件。
回答by Amit
If you are using oracle 10g or greater, you can purge the recycle bin using command
如果您使用的是 oracle 10g 或更高版本，您可以使用命令清除回收站
purge recylebin
or even purge the contents related to the tablespace using
甚至使用清除与表空间相关的内容
purge tablespace
This should free up the space which is deleted but not yet available , please note this would be made available automatically when the space stress occurs for the given tablespace.
这应该释放已删除但尚未可用的空间，请注意，当给定表空间出现空间压力时，这将自动可用。
In addition,
You can use the segment advisor to find all the segments that you can "shrink", and easily reclaim your space.
Read more at
Segment Shrink
此外，您可以使用段顾问找到所有可以“缩小”的段，并轻松收回您的空间。
在Segment Shrink 中了解更多信息
And the last one you can use with locally managed tablespaces
最后一个可以与本地管理的表空间一起使用
Alter Tablespace tablespace_name shrink space
This would free as much space as possible while maintaining other attributes.
这将在保持其他属性的同时释放尽可能多的空间。
回答by sangretu
Rebuild your indexes.
重建索引。
