创建index的时候,报了如下的错误。让人有些摸不着头脑。
create unique index t_pk on t(object_id,object_name) nologging
*
ERROR at line 1:
ORA-01114: IO error writing block to file (block # )
ORA-01114: IO error writing block to file 201 (block # 15437)
ORA-27072: File I/O error
Additional information: 4
Additional information: 15437
Additional information: 20480
但是查看数据文件,没有任何记录。
SQL> SELECT file_name , tablespace_name ,file_id,'datafile' AS TYPE
2 FROM DBA_DATA_FILES where file_id=201
3 /
no rows selected
可以采用如下的语句来查询,发现是temp报错了。
SQL> SELECT file_name , tablespace_name ,file_id,'datafile' AS TYPE
2 FROM DBA_DATA_FILES where file_id=201
3 UNION ALL
4 SELECT file_name , tablespace_name ,file_id+to_number(value) file_id ,'tempfile '
5 FROM DBA_TEMP_FILES , v$parameter p
6 WHERE p.name = 'db_files'
7 /
FILE_NAME TABLESPACE_NAME FILE_ID TYPE
-------------------------------------------------- ------------------------------ ---------- ---------
/u03/ora11g/oradata/TEST01/temp01.dbf TEMPTS1 201 tempfile
查询表空间的使用情况,发现不了任何问题
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
3645037571 TEST01 1 TEST01
Tablespace STA M A Init Total MB Free MB Used MB LrgstMB MaxExt %Fr A
------------ --- - - ---- ------------ ---------- ----------- -------- ------------ --- -
POOL_DATA OLN L S 64K 1,460 1,043 417 499 2147483645 71
SYSAUX OLN L S 64K 325 148 177 146 2147483645 46
SYSTEM OLN L S 64K 325 54 271 53 2147483645 17 *
TEMPTS1 OLN L U 1M 200 200 0 120 100
TEST_DATA1 OLN L S 64K 3 1 2 1 2147483645 35
UNDOTBS OLN L S 64K 1,167 1,152 15 1,138 2147483645 99
------------ ---------- -----------
sum 3,480 2,598 882
查看文件系统情况。
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 3.9G 2.3G 1.4G 62% /
tmpfs 992M 0 992M 0% /dev/shm
/dev/sda1 49M 38M 8.2M 83% /boot
/dev/sda5 32G 834M 30G 3% /home
/dev/sdb1 7.6G 6.5G 715M 91% /u01
/dev/sdb2 7.6G 7.2G 43M 100% /u02
/dev/sdb3 7.6G 7.2G 4.0K 100% /u03
/dev/sdb4 17G 14G 2.8G 83% /u04
在此基础上把空间腾出来一部分。然后将temp空间做了适当的调整,如下。再次创建就没有问题了。
[ora11g@rac1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 3.9G 2.3G 1.4G 62% /
tmpfs 992M 0 992M 0% /dev/shm
/dev/sda1 49M 38M 8.2M 83% /boot
/dev/sda5 32G 834M 30G 3% /home
/dev/sdb1 7.6G 6.0G 1.2G 84% /u01
/dev/sdb2 7.6G 7.2G 43M 100% /u02
/dev/sdb3 7.6G 6.6G 668M 91% /u03
/dev/sdb4 17G 14G 2.8G 83% /u04