今天遇到ORA-01114错误！

最新推荐文章于 2024-12-20 15:44:41 发布

原创最新推荐文章于 2024-12-20 15:44:41 发布 · 5.2k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#sql #database #file #string #数据库

数据库(Oracle/SQL Server) 专栏收录该内容

64 篇文章

订阅专栏

1.今天收到用户的反馈，进入〔物资供应系统－调拨单下帐〕窗口时，遇到如下错误：

2.在http://tahiti.oracle.com查询有关ORA-01114的错误信息如下：

　　　ORA-01114 IO error writing block to file string (block # string)

Cause: The device on which the file resides is probably offline. If the file is a temporary file, then it is also possible that the device has run out of space. This could happen because disk space of temporary files is not necessarily allocated at file creation time.

Action: Restore access to the device or remove unnecessary files to free up space.

3.查看物理磁盘剩余空间还有20多个Ｇ，排除磁盘空间不足的原因。

4.查看各个数据文件大小：

SQL> SELECT bytes/1024/1024/1024 AS "大小(G)",NAME FROM v$datafile ORDER BY bytes;

大小(G)　　 NAME
---------- 　　--------------------------------------------------------------------------------
0.00488281 D:/ORACLE/ORADATA/KDC/TEMP02.ORA
0.01525878 C:/DATAFILE/KDCWZ_PLAN.ORA
0.01678466 D:/ORACLE/ORADATA/KDC/TOOLS01.DBF
0.01953125 D:/ORACLE/ORADATA/KDC/CWMLITE01.DBF
0.01953125 D:/ORACLE/ORADATA/KDC/DRSYS01.DBF
0.01953125 D:/ORACLE/ORADATA/KDC/ODM01.DBF
0.02441406 D:/ORACLE/ORADATA/KDC/INDX01.DBF
0.03723144 D:/ORACLE/ORADATA/KDC/XDB01.DBF
0.14587402 D:/ORACLE/ORADATA/KDC/EXAMPLE01.DBF
0.34765625 D:/DATAFILE/KDCWZ_BILL.ORA
0.41015625 D:/ORACLE/ORADATA/KDC/SYSTEM01.DBF
0.49328613 E:/DATAFILE/KDCWZ_STOCK.ORA
1.50741577 D:/ORACLE/ORADATA/KDC/UNDOTBS01.DBF
2.03735351 D:/ORACLE/ORADATA/KDC/USERS01.DBF

14 行已选择

SQL> SELECT bytes/1024/1024/1024 AS "大小(G)",NAME FROM v$tempfile ORDER BY bytes;

大小(G) 　NAME
---------- --------------------------------------------------------------------------------
8.00000000 D:/ORACLE/ORADATA/KDC/TEMP01.DBF

看到系统中最大数据文件D:/ORACLE/ORADATA/KDC/TEMP01.DBF 大小为8G,初步估计是temp表空间无法扩展的原因。

5.重建temp表空间：

1.以sysdba身份登录

2.创建临时中转的临时表空间TEMP2

　SQL> create temporary tablespace TEMP2 TEMPFILE D:/ORACLE/ORADATA/KDC/TEM
P02.DBF' SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
2 /

表空间已创建。

3.修改默认临时表空间为TEMP2

SQL> alter database default temporary tablespace temp2;

数据库已更改。

4.drop原来的临时表空间TEMP:

SQL> drop tablespace temp including contents and datafiles;

表空间已丢弃。

5.重新创建临时表空间TEMP:

　SQL> create temporary tablespace TEMP TEMPFILE D:/ORACLE/ORADATA/KDC/TEM
P01.DBF' SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
2 /

表空间已创建。

6.更改默认临时表空间为TEMP:

SQL> alter database default temporary tablespace temp;

数据库已更改。

7.drop临时表空间TEMP2

SQL> drop tablespace temp2 including contents and datafiles;

表空间已丢弃。

6.重新进入〔物资供应系统－调拨单下帐〕窗口，成功，问题解决！