今天遇到ORA-01114错误！

 

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.重新进入〔物资供应系统－调拨单下帐〕窗口，成功，问题解决！