oracle 10G 收缩数据文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/391292/
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 10G shrink datafiles
提问by kdgregory
how do i shrink datafiles in oracle 10G?
如何缩小 oracle 10G 中的数据文件？
回答by kdgregory
Caveat: I am not an Oracle system administrator, other than for personal installs. Take everything I say with a large grain of salt.
警告：除了个人安装之外，我不是 Oracle 系统管理员。把我说的一切都放在一边。
I'm assuming that you created the datafiles with auto-extend, and they've been extended past what you feel they should contain. There is a clause to ALTER DATABASE that will resize a file, here's the example from the Oracle SQL reference:
我假设您使用自动扩展创建了数据文件，并且它们已经扩展到您认为它们应该包含的范围之外。ALTER DATABASE 有一个子句可以调整文件大小，这是 Oracle SQL 参考中的示例：
ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;
However, I really don't think you want to do this blindly.A better approach IMO would be to use the export command to dump the tables that are in that datafile, then recreate the tablespace.
但是，我真的不认为您想盲目地这样做。IMO 的更好方法是使用 export 命令转储该数据文件中的表，然后重新创建表空间。
回答by kdgregory
Here is a way, courtesy of Tom Kyte to get the block size of your db, list how much space you can possibly reclaim, and to build the alter... commands to actually perform the database shrinks. Hope this helps,
这是一种方法，由 Tom Kyte 提供，用于获取数据库的块大小，列出可以回收的空间量，并构建 alter... 命令以实际执行数据库收缩。希望这可以帮助，
http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html
http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html
Craig Glendenning
克雷格格伦登宁
回答by user173422
The ALTER TABLESPACE....RESIZE is only allowed beyond the HWM. So you might have many unused segmnets below it. Before this operation, issue an:
ALTER TABLESPACE....RESIZE 只允许超出 HWM。所以它下面可能有许多未使用的段。在此操作之前，发出：
ALTER TABLE .. .SHRINK SPACE on some tables of that tablespace/datafile in order to reorganize the contents of the datafiles.
ALTER TABLE .. .SHRINK SPACE 在该表空间/数据文件的某些表上，以便重新组织数据文件的内容。
Might be a long task, but you can generate the commands with SQL.
可能是一项很长的任务，但您可以使用 SQL 生成命令。
回答by kupa
@Dave's answer about exporting and importing is correct and the best choice to free up the space. Also in Oracle there is no shrink command, @Daveis right but there exists resize command which as @Davidsaid "Data files can be resized down to the last used block" BUT there is one mentionable thing, that free blocks in datafile may be allocated like that
@Dave关于导出和导入的回答是正确的，是释放空间的最佳选择。同样在 Oracle 中没有收缩命令，@Dave是正确的，但存在调整大小命令，正如@David所说“数据文件可以调整到最后使用的块”但是有一件值得一提的事情，数据文件中的空闲块可能是这样分配
0101000001111000000000000000000001110000000000
|---------
where: 0-is free block 1-is used block
其中： 0-是空闲块 1-是使用块
by resizing datafile till last used block it will become like that:
通过调整数据文件直到最后使用的块，它会变成这样：
010100000111100000000000000000000111
|---------
But what about other free blocks in datafile?? They are not available for other datafiles or system itself.
但是数据文件中的其他空闲块呢？它们不可用于其他数据文件或系统本身。
If the datafile were like that:
如果数据文件是这样的：
1111111111111100000000000000000
then resizing would be usefull but not in previous variant.
那么调整大小会很有用，但在以前的变体中无效。
Anyway to identify till what size you are able resize datafile, here is the script:
无论如何要确定您可以调整数据文件的大小，这里是脚本：
select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||
'% full now '||
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))||'%'
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id
Don't worry this script will not cut any used blocks from datafile.
不要担心这个脚本不会从数据文件中删除任何使用过的块。
回答by Dave Costa
For standard datafiles in Oracle, you can't shrink them. You would have to do something like:
对于 Oracle 中的标准数据文件，您无法缩小它们。您必须执行以下操作：
- Move the segments to another tablespace, or export and drop them
- Drop the datafile
- Create a new smaller datafile
- Move the segments back to the first tables, or import them from the dump file
- 将段移动到另一个表空间，或导出并删除它们
- 删除数据文件
- 创建一个新的较小的数据文件
- 将段移回第一个表，或从转储文件中导入它们
For a "bigfile" tablespace -- meaning that CREATE BIGFILE TABLESPACE was used to create it -- you can use ALTER TABLESPACE .. RESIZE ...
对于“大文件”表空间——意味着使用 CREATE BIGFILE TABLESPACE 创建它——你可以使用 ALTER TABLESPACE .. RESIZE ...
