EXP Imp Import Export FAQ

1. When imp imports, there is no table space.

Table space ' EAI1011 ' does not exist in tablespace ' ygzj_data ' does not exist '

webmdata ' of tablespace

Because it is already an import error, I want to remove it and import it again.

Workaround to create a tablespace, delete a table that has already been imported, and re-import the operation

Drop user Jxpcyw CASCADE;
DROP USER EAI CASCADE;  
DROP USER Jxpcjiekou CASCADE;
DROP USER ygzj CASCADE;

CREATE USER "EAI" profile "default" identified by the "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 the "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 the "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";
  

and recreate the table space

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 spaces 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 spaces 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 spaces MANAGEMENT AUTO;

Note: Be sure to turn on automatic expansion at the initial time and make the table space as large as possible

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 spaces MANAGEMENT AUTO;

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

And then import again

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

Next, in the process of importing, the following problems often occur;

Oraclet command line imp times value too large (ORA-12899)
Constant loop, prompt field value too large

View Log

Export files created by export:v11.02.00 through a regular path
Import in the ZHS16GBK character set and Al16utf16 NCHAR character set has been completed
Import server uses Al32utf8 character set (possible character set conversion)

The workaround is: Modify the character set

Solution:

Sql>select * from V$nls_parameters
sql>conn/as sysdba
sql>shutdown
Immediate sql> STARTUP MOUNT
sql>alter SYSTEM ENABLE restricted session  -Single user  If you are sure that no one else is using the machine can be used without control
Sql>alter System set Job_queue_processes=0-  -Canceling a task if you are sure that no one else is using the machine can
sql>alter SYSTEM set aq_tm_processes=0-- Cancel Queue Monitor If you are sure that no one else is using the machine you can do without the
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

Test OK

Sometimes Oracle databases above 11G will not import data with a data volume of 0, which can be handled as follows

--11g a problem with null tables not exported---Generate batch select ' ALTER TABLE ' with this statement for each user to execute | |


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; Perform a batch process