IMP导入数据 报错 IMP-00058 ORA-01691 IMP-00028

 

查看导入日志，发现错误如下：

oracle@sspt:/back/log> cat imp.HD_HDXX.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing GSYY's objects into GSYY
. . importing table                      "HD_HDXX"
IMP-00058: ORACLE error 1691 encountered
ORA-01691: unable to extend lob segment GSYY.SYS_LOB0000076833C00008$$ by 8192 in tablespace TBS_GSYY_DATA
IMP-00028: partial import of previous table rolled back: 3142033 rows rolled back
Import terminated successfully with warnings.

 

 

 

根据日志提示，TBS_GSYY_DATA表空间不足  ,查看表空间使用，发现TBS_GSYY_DATA确实不足：

SQL> select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
  2  from
  3  (select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,
  4  (select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b
  5  where   a.tablespace_name=b.tablespace_name
  6  order   by   ((a.bytes-b.bytes)/a.bytes)   desc;

TABLESPACE_NAME                    Sum MB     used MB       free MB   percent_used
------------------------------ ---------- ---------- ---------- ------------
TBS_GSYY_DATA                         25000      24991.3125     8.6875        99.97
SYSTEM                                         720         719.375             .625               99.91
SYSAUX                                         560        531.875              28.125           94.98
EXAMPLE                                      330         310.125            19.875            93.98
USERS                                          52.5        49.0625           3.4375             93.45
UNDOTBS1                                  5000     164.3125          4835.6875         3.29
PERM_MAIN                                1000      27.9375          972.0625              2.79

 

 

增大表空间

SQL> alter tablespace tbs_gsyy_data add datafile '/home/oracle/ora11g/oradata/tbs_gsyy_data02.dbf'  size 25000m;

Tablespace altered.

 

重新导入表，无异常

oracle@sspt:/back/log>cat imp.HD_HDXX.log

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing GSYY's objects into GSYY
. . importing table                      "HD_HDXX"    3219563 rows imported
Import terminated successfully without warnings.