Exp Imp 导入导出常见问题解决方法

1、IMP导入时出现表空间不存在

表空间 'EAI1011' 不存在

表空间 'YGZJ_DATA' 不存在

表空间 'WEBMDATA' 不存在

因为已经是导入错误了 所以要重新删除后再导入

解决方法 建立表空间 ，删除已经导入的表，重新导入操作

drop user JXPCYW CASCADE;
DROP USER EAI CASCADE;  
DROP USER JXPCJIEKOU CASCADE;
DROP USER YGZJ CASCADE;

CREATE USER "EAI" PROFILE "DEFAULT" IDENTIFIED BY "EAI" DEFAULT TABLESPACE "EAI" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "EAI";
GRANT "DBA" TO "EAI";
GRANT "RESOURCE" TO "EAI";

CREATE USER "YGZJ" PROFILE "DEFAULT" IDENTIFIED BY "YGZJ" DEFAULT TABLESPACE "YGZJ" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "YGZJ";
GRANT "DBA" TO "YGZJ";
GRANT "RESOURCE" TO "YGZJ";

CREATE USER "JXPCYW" PROFILE "DEFAULT" IDENTIFIED BY "JXPCYW" DEFAULT TABLESPACE "JXPCYW" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "JXPCYW";
GRANT "DBA" TO "JXPCYW";
GRANT "RESOURCE" TO "JXPCYW";

CREATE USER "JXPCJIEKOU" PROFILE "DEFAULT" IDENTIFIED BY "JXPCJIEKOU" DEFAULT TABLESPACE "JXPCJIEKOU" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "JXPCJIEKOU";
GRANT "DBA" TO "JXPCJIEKOU";
GRANT "RESOURCE" TO "JXPCJIEKOU";

然后重新创建表空间

CREATE SMALLFILE TABLESPACE "EAI1011" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAI101101.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "YGZJ_DATA" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\YGZJ_DATA01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

CREATE SMALLFILE TABLESPACE "WEBMDATA" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\WEBMDATA01.dbf' SIZE 1G AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

注意：一定要在初始的时候开启自动扩展 然后表空间尽量大 尽量大

CREATE SMALLFILE TABLESPACE "EAI" DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAI01.dbf' SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 4G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

ALTER TABLESPACE "EAI" ADD DATAFILE 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\EAI02.DBF' SIZE 10G

然后重新导入

imp jxpcyw/XXXXX file=/home/oracle/jxpcyw_2016-06-02.dmp ignore=y fromuser=JXPCYW touser=jxpcyw grants=n log=jxpcyw20160602.log

imp eai/XXXX file=/home/oracle/jxpceai_2016-06-02.dmp ignore=y fromuser=EAI touser=eai grants=n log=eai20160602.log

imp jxpcjiekou/XXXXXX file=/home/oracle/jxpcjk_2016-06-02.dmp ignore=y fromuser=JXPCJIEKOU touser=jxpcjiekou grants=n log=JK20160602.log data_only=n ;

imp ygzj/XXXXX file=/home/oracle/jxpcygzj_2016-06-02.dmp ignore=y fromuser=YGZJ touser=ygzj grants=n log=ygzj20160602.log

接下来在导入的过程中，经常会出现如下问题；

Oraclet命令行imp时报值太大(ORA-12899)
不停的循环，提示字段值太大

查看日志

经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
导入服务器使用 AL32UTF8 字符集 (可能的字符集转换)

解决办法是：修改字符集

解决办法：

SQL>select * from V$NLS_PARAMETERS
SQL>conn / as sysdba
SQL>shutdown immediate
SQL>STARTUP MOUNT
SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION  --单用户  如果是确定没有其他人在用的机器可以不用管
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0  --取消任务 如果是确定没有其他人在用的机器可以不用管
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0 --取消queue monitor 如果是确定没有其他人在用的机器可以不用管
SQL>ALTER DATABASE OPEN
SQL>ALTER DATABASE CHARACTER SET ZHS16GBK
SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK

ALTER SYSTEM DISABLE RESTRICTED SESSION
SQL>SHUTDOWN IMMEDIATE
SQL>STARTUP

测试OK

有时候ORACLE 11G 以上的数据库会不导入数据量为0的数据，可以如下处理

--11g 出现了空表不导出的问题 ---每个用户都要执行的 
用 这个语句 生成批处理
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;


alter table T_TASK allocate extent;
alter table T_SIMPLE_QUEUE allocate extent;
alter table T_JMS_SUBSCRIPTIONS allocate extent;
alter table T_JMS_QUEUE allocate extent;
alter table T_JMS_EVENTS allocate extent;
alter table T_JMS_DESTINATIONS allocate extent;
alter table TPALOCK allocate extent;
执行批处理