SQL ORA 01114 - 将块写入文件的 IO 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20961832/
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
ORA 01114 - IO Error writing block to file
提问by CloudJedi
I am working on some compelex sql queries in Oracle 11g, which have aggregation functions like SUM and joins multiple tables and views. I am getting the IO Error and Tablespace insufficient space error when I try to query large span of data.
我正在 Oracle 11g 中处理一些 compelex sql 查询，这些查询具有 SUM 等聚合函数并连接多个表和视图。当我尝试查询大量数据时，出现 IO 错误和表空间空间不足错误。
Error no.1 is ORA-01114: IO Error writing block to file(block #) ORA-01114: IO Error writing block to file 201(block #1343798) ORA-27063: number of bytes read/written is incorrect
错误 1 是 ORA-01114：IO 错误将块写入文件（块 #） ORA-01114：IO 将块写入文件 201 时出错（块 #1343798） ORA-27063：读取/写入的字节数不正确
Error no.2 Sometimes Database Running out of Temporary Space when loaddate > 12 months
错误 2 当 loaddate > 12 个月时，有时数据库会耗尽临时空间
Is this an Oracle specific error that my DBA has to solve or something is wrong with my queries? How would I fine tune the performance of the queries to avoid insufficient tablespace prompt? I am writing a dummy sample of what my queries look like
这是我的 DBA 必须解决的 Oracle 特定错误还是我的查询有问题？我将如何微调查询的性能以避免表空间提示不足？我正在写一个我的查询看起来像的虚拟示例
SELECT Sum(s.stock + s.accept + s.new) AS result,
Floor(( s.sales / s.stock ) * 100) AS sales_per,,
f.load_date,,
u.user_id,
Sum(s.falsepos + s.realvio) AS closed_ale,
Sum(f.nbrecords) AS nb_records
FROM stocks s,
facts f,
zones z,
users u
WHERE s.sid = f.fid
AND z.zoneid = f.zoneid
AND u.userid = z.userid
AND f.load_date BETWEEN '20081010' AND '20121030'
采纳答案by Todd
Yes, you'll probably have to get your DBA to fix something.
是的，您可能需要让您的 DBA 修复某些问题。
According to this page,
根据这个页面，
ORA-01114 occurs when you attempt to write to a file and the device with the file is either:
1) offline OR
2) Has run out of space, possibly because it is a temporary file which was not allocated at creation time.
To resolve ORA-01114, you should either:
1) restore access to the device OR
2) take out files which are not needed in order to gain more space
ORA-01114 当您尝试写入文件并且包含该文件的设备是：
1) 离线或
2) 空间不足，可能是因为它是一个在创建时未分配的临时文件。
要解决 ORA-01114，您应该：
1) 恢复对设备的访问或
2）取出不需要的文件以获得更多空间
And offers this debugging advice (I've inserted your block numbers):
并提供此调试建议（我已插入您的块号）：
You can pinpoint the tablespace and segment for an ORA-01444 error by plugging-in the file_id and block_id into this query on dba_extents:
您可以通过将 file_id 和 block_id 插入到 dba_extents 上的此查询中来查明 ORA-01444 错误的表空间和段：
select
owner,
tablespace_name,
segment_type,
segment_name
from
dba_extents
where
file_id = 201
and
block_id = 1343798;
回答by Kazugi
This command may save you guys.
这个命令可能会拯救你们。
alter tablespace temp shrink space keep 40m;
改变表空间临时收缩空间保持40m；
Perhaps your system reserve space for temp too much. So we just free this space, we may get back available resources to operate properly.
也许您的系统为 temp 保留了太多空间。所以我们只是释放这个空间，我们可能会取回可用资源来正常运行。
