使用/不使用删除存储的 Oracle 截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1972145/
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 truncate with / without drop storage
提问by denisb
Tried to get a bit more info on this, but figured I'd just ask.... I attempted to perform a table truncate on Oracle 10g with the drop storage, however the results aren't entirely what I was expecting.
试图获得更多有关此的信息，但我想我只是问.... 我尝试在 Oracle 10g 上使用 drop 存储执行表截断，但是结果并不完全符合我的预期。
Basically, the table in question had over 30 million records and over 3Gb allocated for it, but somehow the space allocated for it still appears to be used. Is there a way to actually drop the allocated space used, or am I going about it the wrong way?
基本上，有问题的表有超过 3000 万条记录和超过 3Gb 分配给它，但不知何故分配给它的空间似乎仍然被使用。有没有办法实际删除已使用的分配空间，或者我是否以错误的方式处理它？
===EDIT====
===编辑====
My bad...I was referring to physical disk space, in terms of the datafile size... At the moment our export / backup amounted to about 13GB, so the intention here was to shrink the actual file in the process of truncating the data when we export to a file for backup.
我的不好...我指的是物理磁盘空间，就数据文件大小而言...目前我们的导出/备份大约为 13GB，所以这里的目的是在截断文件的过程中缩小实际文件当我们导出到文件进行备份时的数据。
采纳答案by CMG
You don't exactly say what you were expecting to happen when you truncated the table. Remember that if you de-allocate the storage from a table/index then it's just returned to the tablespace as free-space. The space used on disk from the operating system's perspective isn't going to get any smaller. So whilst a tablespace can be set to "auto extend" when more space is required, it does not auto-shrink when space is freed.
当您截断表格时，您并没有完全说出您期望发生的事情。请记住，如果您从表/索引中取消分配存储，那么它只是作为可用空间返回到表空间。从操作系统的角度来看，磁盘上使用的空间不会变小。因此，虽然在需要更多空间时可以将表空间设置为“自动扩展”，但在释放空间时它不会自动收缩。
回答by Rajesh Chamarthi
What kind of extent management and segment_space_management are you using?
你使用什么样的盘区管理和segment_space_management？
select tablespace_name from user_segments
where segment_name = 'T' ---T here would b your table name
TABLESPACE_NAME
------------------------------
TS_USERS_01
sql > select TABLESPACE_NAME, EXTENT_MANAGEMENT, SEGMENT_SPACE_MANAGEMENT
2 from dba_tablespaces where tablespace_name = 'TS_USERS_01';
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
TS_USERS_01 LOCAL AUTO
Are you using dictionary managed tablespaces or locally managed tablespaces with system allocated segments ?
您使用的是字典管理的表空间还是带有系统分配段的本地管理的表空间？
