Restore Oracle database DMP file (win system)



   1. Check data character set:

Generally, when Oracle is installed, ZHS16GBK is selected by default. If there is any change, select userenv (‘language ‘) from dual; statement is used to check the character set used to ensure that the character sets on both sides are consistent.

In the case of inconsistency, when importing data, it may return to the situation that the length of the field becomes longer, leading to data writing failure, or even garbled.


   2. Create a table space:

When creating a table space, you can create it according to the size of the file you need to import

Method 1: directly give the table space with the size of imported data (my nearly 60 g)

Syntax: create tablespace name datafile ‘data file name’ size tablespace size

Example: create tablespace atmcs_ 01  datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\ATMCS_ 01.bdf’ size 30000m;

Supplementary note: there is an upper limit for the size of the table space, and each file is no more than 32GB (the exact value is 32768m). Therefore, when the data to be imported is greater than the maximum table space value, we can use the following statement:

alter tablespace ATMCS_ 01 add datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\ATMCS_ 02. BDF ‘size 30000m; add the table space size

       From the above statement, it is not difficult to find that the table space requirements are consistent, the file name is not the same name, and the additional size is determined according to their own needs. If it’s not enough, add more.

Method 2: self growing table space

Syntax: create tablespace name datafile ‘data file name’ size initial size AUTOEXTEND on next size maxsize unlimited

  Example: create tablespace atmcs_ 01 datafile ‘E:\APP\ADMINISTRATOR\ORADATA\ORCL\ATMCS_ 02.bdf’  size 200m  autoextend on next 10m  maxsize unlimited

     Supplementary note: from the statement point of view, it is the maximum unlimited, in fact, it is not more than 32GB. I used to use self growth table space to have insufficient table space before, so I simply opened it to the maximum.


    3. To create a user specified table space:

Syntax: create user user name “identified by password” default tablespace name;

Example: create user test identified by admin123456 default tablespace atmcs_ 01;


    4: User authorization:

Grammar: Grantconnect,resource,dba To user name;

Example: Grantconnect,resource,dba to TEST  ;

Start import:

 Import method 1:

Win + R wake up CMD directly

Syntax: imp user name / password @ service name file =’dmp file address’log =’import log storage address’full = y ignore = y

Example: imp test/ [email protected]  file=”D:\data\oracle\2020-11-27.dmp”  log=”D:\data\oracle\ test.log ” full =y ignore=y;

   Import method 2:

Using PLSQL, Tools > Import tables > select executable file > select import file in the lower right corner > click Import

When you select an executable file, the executable file is on the installation disk: APP / administrator / product / 11.2.0 / dbhome_ 1\bin\ imp.exe In the middle, my

It’s in D: app, administrator, product, 11.2.0, dbhome_ 1\bin\ imp.exe

When entering the import table, you can select by default, unless you have your own special requirements, such as no longer need the constraint relationship, you can cancel the check

Supplementary note: the two methods are consistent in principle. PLSQL is relatively stable, and some permission problems will be removed. In more cases, you need to try it yourself. No matter whether all of the above is true or not, remember to come back to comment and share with yourself

Oracle data when the experience or problems.

End of import:

During the import process, we will encounter various problems, such as imp-00017: due to Oracle error 2153, imp-00017: due to Oracle error 959… And so on. In a word, we can solve whatever problems we encounter, if we don’t ask




Record and analyze some strange knowledge.



Recommended Today

Large scale distributed storage system: Principle Analysis and architecture practice.pdf

Focus on “Java back end technology stack” Reply to “interview” for full interview information Distributed storage system, which stores data in multiple independent devices. Traditional network storage system uses centralized storage server to store all data. Storage server becomes the bottleneck of system performance and the focus of reliability and security, which can not meet […]