IMP-00003: ORACLE error 1114 encountered
ORA-01114: IO error writing block to file %s (block # %s)
在IMP数据的时候出现:
IMP-00017: following statement failed with ORACLE error 1114:
"ALTER TABLE "CRM_CUSTOMER_BINDING_DRAFT" ADD CONSTRAINT "CRM_CUST_BIND_DRA"
"FT_D_D_UK" UNIQUE ("DOMAIN_LOGIN_ID", "STATUS", "DOMAIN_PREFIX") USING INDE"
"X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FR"
"EELIST GROUPS 1) LOGGING ENABLE "
IMP-00003: ORACLE error 1114 encountered
ORA-01114: IO error writing block to file %s (block # %s)
IMP-00017: following statement failed with ORACLE error 3114:
"ALTER TABLE "CRM_CUSTOMER_BINDING_DRAFT" ADD CONSTRAINT "CRM_CUST_BIND_DRA"
"FT_U_D_UK" UNIQUE ("USER_ID", "STATUS", "DOMAIN_PREFIX") USING INDEX PCTFRE"
"E 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST G"
"ROUPS 1) TABLESPACE "BOSS_DATA" LOGGING ENABLE "
IMP-00003: ORACLE error 3114 encountered
ORA-03114: not connected to ORACLE
IMP-00000: Import terminated unsuccessfully |
略过INDEX,将数据重新导入后,手动加约束,还是报错:
alter table CRM_CUSTOMER_BINDING_DRAFT add constraint CRM_CUST_BIND_DRAFT_D_D_UK unique (DOMAIN_LOGIN_ID,DOMAIN_PREFIX,STATUS) using index CRM_CUST_BINDING_DRAFT_D_D_UK ;
ERROR at line 1:
ORA-01114: IO error writing block to file %s (block # %s) |
alter table CRM_CUSTOMER_BINDING_DRAFT add constraint CRM_CUST_BIND_DRAFT_D_D_UK unique (DOMAIN_LOGIN_ID,DOMAIN_PREFIX,STATUS) using index CRM_CUST_BINDING_DRAFT_D_D_UK
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01114: IO error writing block to file 201 (block # 101862)
ORA-27072: skgfdisp: I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 101861
ORA-01114: IO error writing block to file 201 (block # 101862)
ORA-27072: skgfdisp: I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 101861 |
根据上面的错误,意思是系统空间满,临时表空间不能扩展.
--查看当前临时段的使用情况
select">boss@SIMU>select * from v$sort_usage;
no rows selected |
--查看临时文件名
select">boss@SIMU>select file_name from dba_temp_files;
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/logdata/temp01.dbf |
--查看系统空间
df">admin@simulate:/logdata>df -h
Filesystem Size Used Avail Use% Mounted on
/dev/cciss/c0d0p6 85G 70G 11G 88% /
/dev/cciss/c0d0p1 289M 18M 257M 7% /boot
none 9.8G 0 9.8G 0% /dev/shm
/dev/cciss/c0d0p5 20G 2.9G 16G 16% /opt
/dev/cciss/c0d1p1 539G 323G 189G 64% /oradata1
/dev/cciss/c0d0p3 29G 28G 0 100% /logdata
/dev/sda1 822G 689G 125G 85% /exp_bak
du">admin@simulate:/logdata>du -h temp01.dbf
796M temp01.dbf
ll">admin@simulate:/logdata>ll
total 28622076
-rw-r----- 1 oracle oinstall 7954432 Nov 11 16:47 control01.ctl
-rw-r----- 1 oracle oinstall 7954432 Nov 11 16:47 control02.ctl
-rw-r----- 1 oracle oinstall 7954432 Nov 11 16:47 control03.ctl
-rw-r----- 1 oracle oinstall 104858112 Nov 11 16:42 redo01.log
-rw-r----- 1 oracle oinstall 104858112 Nov 11 16:47 redo02.log
-rw-r----- 1 oracle oinstall 104858112 Nov 11 16:42 redo03.log
-rw-r----- 1 oracle oinstall 1048584192 Nov 11 16:42 rman_tbs01.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:42 simu01.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:42 simu02.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:42 simu03.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:42 simu04.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:42 simu05.dbf
-rw-r----- 1 oracle oinstall 2146443264 Nov 11 16:42 simu06.dbf
-rw-r----- 1 oracle oinstall 2146443264 Nov 11 16:42 simu07.dbf
-rw-r----- 1 oracle oinstall 2146443264 Nov 11 16:42 simu08.dbf
-rw-r----- 1 oracle oinstall 2146443264 Nov 11 16:42 simu09.dbf
-rw-r----- 1 oracle oinstall 2146443264 Nov 11 16:42 simu10.dbf
-rw-r----- 1 oracle oinstall 2146443264 Nov 11 16:42 simu11.dbf
-rw-r----- 1 oracle oinstall 536879104 Nov 11 16:42 system01.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:37 temp01.dbf
-rw-r----- 1 oracle oinstall 536879104 Nov 11 16:42 tools01.dbf
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:42 undotbs01.dbf
-rw-r----- 1 oracle oinstall 524296192 Nov 11 16:42 undotbs02.dbf |
从上面的数据可以看出,错误的直接原因还是/logdata空间使用为100%.
临时解决:
1. 增加一个新的临时表空间TEMP2,
2. 设置TEMP2为数据库默认的临时表空间.
那上面的情况到底是一个什么场景呢?
1.我们查看临时段没有人使用.(按理说2G的临时空间都可以给我用)
2.当进行添加约束时,由于表比较大(1000W行),需要的临时段很大(大于796M),所以ORACLE向系统申请空间.
3.但这时,/logdata/ 已经满了. 所以就报错了.
有人会问:
-rw-r----- 1 oracle oinstall 2097160192 Nov 11 16:37 temp01.dbf |
这里明明显示TEMP01.dbf是2G容量,为什么会796M呢?
在这里要补充的是:
当我们在建立临时表空间的时候, 比如大小为2G，在建立完成后,用LS查看,也显示为2G. (因为LS读的是文件列表HEAD)。但实际并没有占用这大的空间(可以用du -h 来查看直接的使用空间。)只有当ORACLE需要使用TEMP的时候,才向系统申请空间.(所以我们在建临时表空间的时候,速度特别快,哪怕是10G,20G的文件都这样快。)
下面我们用实例来看一下:
col bytes format 99999999999
col file_name format a40
select file_name,bytes from dba_temp_files;
FILE_NAME BYTES
---------------------------------------- ------------
/opt/oracle/oradata/xplat/temp1_01.dbf 2147483648 |
可以看到, 给TEMP表空间加一个新的临时文件。
我们来看看,这个文件实际占用了多少空间。
18:47:04 @>alter TABLESPACE temp1 add tempfile '/opt/oracle/oradata/xplat/temp1_02.dbf' size 500M;
Tablespace altered.
Elapsed: 00:00:00.05
18:47:50 @>select file_name,bytes from dba_temp_files;
FILE_NAME BYTES
---------------------------------------- ------------
/opt/oracle/oradata/xplat/temp1_02.dbf 524288000
/opt/oracle/oradata/xplat/temp1_01.dbf 2147483648
18:51:17 @>host ls /opt/oracle/oradata/xplat/temp1_02.dbf -l
-rw-r----- 1 oracle oinstall 524296192 Nov 11 18:47 /opt/oracle/oradata/xplat/temp1_02.dbf
18:51:24 @>host du -h /opt/oracle/oradata/xplat/temp1_02.dbf
80K /opt/oracle/oradata/xplat/temp1_02.dbf |
这里的数据完全符合我们上面所说的。