blocks|key|6049717|text|警告:除个人安装外，我不是Oracle系统管理员。我说的每一句话都要持保留态度。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6049718|我假设您使用自动扩展创建了数据文件，并且它们已经扩展到您认为它们应该包含的范围之外。有一个用于更改数据库的子句，它将调整文件的大小，下面是Oracle+SQL参考中的示例：|6049719|ALTER+DATABASE
DATAFILE+'diskb:tbs_f5.dat'+RESIZE+10+M;|code-block|syntax|javascript|6049720|但是，我真的不认为您想盲目地这样做。更好的方法是使用export命令转储数据文件中的表，然后重新创建表空间。|6049721|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Caveat: I am not an Oracle system administrator, other than for personal installs. Take everything I say with a large grain of salt. 

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:

<pre><code>ALTER DATABASE
DATAFILE 'diskb:tbs_f5.dat' RESIZE 10 M;
</code></pre>

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.

blocks|key|6049738|text|这是一种方法，由Tom+Kyte提供，可以获取数据库的块大小，列出您可以回收的空间，并构建更改...实际执行数据库收缩的命令。希望这能帮上忙|type|unstyled|depth|inlineStyleRanges|entityRanges|data|6049739|http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html|offset|length|6049740|克雷格·格伦登宁|6049741|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|2G|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@$D|Q|E|R|1|S]]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|U|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|C]]]]

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, 

<a href="http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html" rel="nofollow noreferrer">http://cglendenningoracle.blogspot.com/2009/08/how-do-i-shrink-datafiles-to-reclaim.html</a>

Craig Glendenning

blocks|key|4632961|text|ALTER+TABLESPACE....RESIZE仅允许超出HWM。因此，在它下面可能有许多未使用的segmnets。在此操作之前，发出以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4632962|更改表..表空间/数据文件的某些表上的.SHRINK空间，以便重新组织数据文件的内容。|4632963|这可能是一项很长的任务，但您可以使用SQL生成命令。|4632964|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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 TABLE .. .SHRINK SPACE on some tables of that tablespace/datafile in order to reorganize the contents of the datafiles.

Might be a long task, but you can generate the commands with SQL.

blocks|key|4632986|text|@Dave关于导出和导入的回答是正确的，并且是释放空间的最佳选择。另外，在Oracle中没有收缩命令，@Dave是正确的，但是存在resize命令，正如@Dave所说的“数据文件可以缩小到最后使用的块”，但是有一点值得一提的是，数据文件中的空闲块可以像这样分配|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|4632987|0101000001111000000000000000000001110000000000
++++++++++++++++++++++++++++++++++++%7C---------|code-block|syntax|javascript|4632988|其中:0-为空闲数据块1-为已用数据块|4632989|通过调整数据文件大小直到最后使用的数据块，它将变成这样：|4632990|010100000111100000000000000000000111
++++++++++++++++++++++++++++++++++++%7C---------+++++|4632991|但是数据文件中的其他空闲块呢？它们不适用于其他数据文件或系统本身。|4632992|如果数据文件是这样的：|4632993|1111111111111100000000000000000|4632994|那么调整大小将是有用的，但在以前的变体中不是这样的。|4632995|无论如何，为了确定您可以调整数据文件的大小，下面是脚本：|4632996|++++select+'alter+database+'%7C%7Ca.name%7C%7C'+datafile+'''%7C%7Cb.file_name%7C%7C''''+%7C%7C
'+resize+'%7C%7Cgreatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))%7C%7Cchr(10)%7C%7C
'--tablespace+was+'%7C%7Ctrunc(bytes_full*100/bytes_total)%7C%7C
'%25+full+now+'%7C%7C
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))%7C%7C'%25'
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|4632997|不要担心这个脚本不会从数据文件中剪切任何使用过的块。|4632998|entityMap^0|0|5|1F|5|24|5|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@$9|17|A|18|B|C]|$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|1D|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1E|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|1F|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|1G|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1H|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1I|8|@]|D|@]|E|$]]|$1|U|3|V|5|H|7|1J|8|@]|D|@]|E|$I|J]]|$1|W|3|X|5|6|7|1K|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|1L|8|@]|D|@]|E|$]]|$1|10|3|11|5|H|7|1M|8|@]|D|@]|E|$I|J]]|$1|12|3|13|5|6|7|1N|8|@]|D|@]|E|$]]|$1|14|3|-4|5|6|7|1O|8|@]|D|@]|E|$]]]|15|$]]

@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, @Dave is right but there exists resize command which as @David said "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

<pre><code>0101000001111000000000000000000001110000000000
 |---------
</code></pre>

where:
0-is free block
1-is used block 

by resizing datafile till last used block it will become like that:

<pre><code>010100000111100000000000000000000111
 |--------- 
</code></pre>

But what about other free blocks in datafile?? They are not available for other datafiles or system itself. 

If the datafile were like that:

<pre><code>1111111111111100000000000000000
</code></pre>

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:

<pre><code> 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 &gt; 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 &lt; .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id
</code></pre>

Don't worry this script will not cut any used blocks from datafile.

blocks|key|1878177|text|对于Oracle中的标准数据文件，您不能收缩它们。你必须做一些类似的事情：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1878178|将数据段移动到另一个表空间，或将其导出并删除，然后删除datafile|1878179|Create+a|unordered-list-item|1878180|ordered-list-item|1878181|将数据段移回第一个表，或从转储文件|1878182|导入它们|1878183|1878184|对于"+BIGFILE“表空间--意思是CREATE+BIGFILE+tablespace用于创建它--您可以使用ALTER+TABLESPACE。调整大小...|1878185|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|T|8|@]|9|@]|A|$]]|$1|G|3|-4|5|H|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|H|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Y|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|Q|$]]

For standard datafiles in Oracle, you can't shrink them. You would have to do something like:

<ol>
<li>Move the segments to another tablespace, or export and drop them</li>
<li>Drop the datafile</li>
<li>Create a new smaller datafile</li>
<li>Move the segments back to the first tables, or import them from the dump file</li>
</ol>

For a "bigfile" tablespace -- meaning that CREATE BIGFILE TABLESPACE was used to create it -- you can use ALTER TABLESPACE .. RESIZE ...

how do i shrink datafiles in oracle 10G?

oracle 10G shrink datafiles

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

如何缩小oracle 10G中的数据文件？

问oracle 10G压缩数据文件
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问oracle 10G压缩数据文件EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问oracle 10G压缩数据文件
EN