EXPDP and IMPDP Export import in Oracle databases

EXPDP and IMPDP are the newly added export and import capabilities of Oracle starting from 10g. Name is called data pump.

Expdp:data Pump Export
Impdp:data Pump Import

The difference between EXPDP, IMPDP and exp, imp

EXPDP, IMPDP:
1, you can import export single or multiple databases, users (schema), table space, tables.
2, powerful data filtering function.
3, fast.
4, does not support xmltype data.

EXP, IMP:
1, support xmltype data.
2. Float and double data types are not supported.
3, the function and the data pump is similar, the more recommended use data pump unless is xmltype data.

Second, export Import method

Small experiment: To export the HR user data, create a new user hr2, and then import the new user.

1. Create directory objects
A directory is a database object, which is an alias to a directory on the physical host file system.

Mkdir/tmp/expdata
chmod 777/tmp/expdata

Also note that Oracle users have read and write access to the exported directory.

sql> Create or replace directory Dmpdir as '/tmp/expdata ';

2. Grant Authority

Sql> Grant Read,write on directory dmpdir to HR;

3. Export

sql> EXPDP hr/hr schemas=hr directory=dmpdir dumpfile=expdp.dmp;

Starting "HR". My_job ": hr/******** schemas=hr directory=dmpdir dumpfile=expdp.dmp logfile=expdp.log job_name=my_job
Estimate in progress using BLOCKS method ...
Processing Object Type Schema_export/table/table_data
Total estimation using BLOCKS method:512 KB
Processing Object Type Schema_export/pre_schema/procact_schema
Processing Object Type Schema_export/sequence/sequence
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/index/index
Processing Object Type Schema_export/table/constraint/constraint
Processing Object Type Schema_export/table/index/statistics/index_statistics
Processing Object Type Schema_export/table/comment
Processing Object Type Schema_export/procedure/procedure
Processing Object Type Schema_export/procedure/alter_procedure
Processing Object Type Schema_export/view/view
Processing Object Type Schema_export/table/constraint/ref_constraint
Processing Object Type Schema_export/table/trigger
Processing Object Type Schema_export/table/statistics/table_statistics
. . Exported "HR". COUNTRIES "6.367 KB rows
. . Exported "HR". Departments "7.007 KB rows
. . Exported "HR". EMPLOYEES "16.80 KB rows
. . Exported "HR". JOBS "6.992 KB rows
. . Exported "HR". Job_history "7.054 KB rows
. . Exported "HR". Locations "8.273 KB rows
. . Exported "HR". Regions "5.476 KB 4 rows
. . Exported "HR". test_t "6.796 KB rows
Master table "HR". " My_job "Successfully loaded/unloaded
******************************************************************************
Dump file set for HR. My_job is:
/tmp/expdata/expdp.dmp
Job "HR". " My_job "successfully completed at 09:07:28

4. Create User HR2

--USER SQL
CREATE USER HR2 identified by HR2
DEFAULT tablespace "USERS"
Temporary tablespace "TEMP";

--QUOTAS
ALTER USER hr2 QUOTA Unlimited on USERS;

--ROLES
GRANT "CONNECT" to HR2;
GRANT "RESOURCE" to HR2;

--SYSTEM privileges
GRANT CREATE any INDEX to HR2;
GRANT CREATE VIEW to HR2;
GRANT CREATE session to HR2;

5, Import

Sql> Grant Read,write on directory Dmpdir to HR2;
sql> IMPDP hr2/hr2 schemas=hr remap_schema=hr:hr2 directory=dmpdir dumpfile=expdp.dmp logfile=impdp.log job_name=my _JOD2;

Master table "HR2". MY_JOD2 "Successfully loaded/unloaded
Starting "HR2". My_jod2 ": hr2/******** schemas=hr remap_schema=hr:hr2 directory=dmpdir dumpfile=expdp.dmp logfile=impdp.log job_name= My_jod2
Processing Object Type Schema_export/pre_schema/procact_schema
Processing Object Type Schema_export/sequence/sequence
Processing Object Type Schema_export/table/table
Processing Object Type Schema_export/table/table_data
. . Imported "HR2". COUNTRIES "6.367 KB rows
. . Imported "HR2". Departments "7.007 KB rows
. . Imported "HR2". EMPLOYEES "16.80 KB rows
. . Imported "HR2". JOBS "6.992 KB rows
. . Imported "HR2". Job_history "7.054 KB rows
. . Imported "HR2". Locations "8.273 KB rows
. . Imported "HR2". Regions "5.476 KB 4 rows
. . Imported "HR2". test_t "6.796 KB rows
Processing Object Type Schema_export/table/index/index
Processing Object Type Schema_export/table/constraint/constraint
Processing Object Type Schema_export/table/index/statistics/index_statistics
Processing Object Type Schema_export/table/comment
Processing Object Type Schema_export/procedure/procedure
Processing Object Type Schema_export/procedure/alter_procedure
Processing Object Type Schema_export/view/view
Processing Object Type Schema_export/table/constraint/ref_constraint
Processing Object Type Schema_export/table/trigger
Processing Object Type Schema_export/table/statistics/table_statistics
Job "HR2". MY_JOD2 "successfully completed at 09:24:39

Remap_schema represents importing from user hr to User hr2.