Oracle database Import and Export method

Oracle database 10g introduces the latest data Dump technology, enabling DBAs or developers to quickly move database metadata (object definitions) and data to another Oracle database.

The role of Data Pump export import (EXPDP and IMPDP)

1. Implement logical backup and logical recovery.

2. Move objects between database users.

3. Moving objects between databases

4, realize the table space move.

the difference between a data pump export import and a traditional export import:

Prior to 10g, traditional export and import using exp tools and IMP tools, starting with 10g, not only retained the original exp and IMP tools, but also provided the data Pump Export Import Tool EXPDP and IMPDP. Things to be aware of when using EXPDP and IMPDP;

Exp and IMP are customer segment tool programs that can be used either on the client or in the server segment.

EXPDP and IMPDP are server-side utilities that can only be used on the Oracle server and cannot be used on the client side.

IMP applies only to the EXP export file, not to the EXPDP export file; IMPDP only applies to EXPDP export files, not to exp export files.


Today I am importing my colleague to my data file, always reported IMP-00038 error, hint, cannot be converted to the environment character set handle, the cause of this error is generally database version mismatch, but we are using oracle11g, we ruled out this possibility.The final reason is that he is using data pump to export data files, and I use the traditional IMP command import is not possible, subsequent to use IMPDP Data pump import success.

Here's the complete process of importing data files using a data pump:

1. Use the system user to log in to the database, create a new user usertest (the user name is the best when creating a new user and the user name is the same as the data file export), the tablespace can be used by default or create a new table space yourself. The SQL code is as follows:

New user uses Oracle default tablespace:
Create user usertest identified by Usertest;

New Table Space:
Create Tablespace User_all
DataFile ' d:/dev/oracle/tablespace/user_all.dbf '
Size 300M
Autoextend on
Next 50M
MaxSize Unlimited

New User (set default tablespace):
Create user usertest identified by usertest default Tablespace user_all;

2, the new user to assign permissions, DBA, connect, resource. The SQL code is as follows:

grant Connect, resource,dba to Usertest;
 
3, Import and export for database, new directory file such as Dumpdir
 
Create directory directory name as ' a directory on database server ', such as:
Create directory Dumpdir as ' d:\ server directory name ';
Place the imported or exported files in this directory
 
4, read and write permissions for this user's import and export directory
Grant Read,write on directory Dumpdir to Usertest;

 
5, Export and import
to SID=ORCL, export DMP account is usertest, import DMP account for Usertest for example.

&NBSP
Export data from SFZ:
Expdp usertest/[email protected]  directory=dumpdir  dumpfile= Export file name

 
Import into usertest:
Impdp usertest/[email protected]  directory=dumpdir  dumpfile= Export file name. DMP
 
Import Export user name is best, the user name is not the same, you need to make a mapping
 
Remap_ schema=test:usertest 

Note When you use the local database, you start the following Oracle services:

Oracleoradb11g_home1tnslistener
Oracleserviceorcl

If you are simply connecting to a database on a remote server, using the PL/SQL tool only, you can stop the service so that you can save some memory.

Oracle Database Import and Export method