expdp和impdp是oracle从10g开始新增加的导出、导入功能。名称叫数据泵。
expdp：Data Pump Export
impdp：Data Pump Import
一、expdp、impdp和exp、imp的区别
expdp、impdp：
1、可以导入导出单个或多个数据库、用户(schema)、表空间、表。
2、强大的数据过滤功能。
3、速度快。
4、不支持XMLType数据。
exp、imp：
1、支持XMLType数据。
2、不支持FLOAT和DOUBLE数据类型。
3、功能和数据泵类似，更推荐使用数据泵除非是XMLType数据。
二、导出导入方法
小实验：将hr用户数据导出，新建用户hr2，再导入新建用户。
1、创建目录对象
目录是数据库对象，它是一个在物理主机文件系统上的目录的别名。
mkdir /tmp/expdata
chmod 777 /tmp/expdata
同时要注意oracle用户对导出目录要有读写权限。
SQL> create or replace directory dmpdir as '/tmp/expdata';
2、授予权限
SQL> grant read,write on directory dmpdir to hr;
3、导出
expdp hr/hr schemas=hr directory=dmpdir dumpfile=expdp.dmp logfile=expdp.log job_name=my_job;
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 25 rows . . exported "HR"."DEPARTMENTS" 7.007 KB 27 rows . . exported "HR"."EMPLOYEES" 16.80 KB 107 rows . . exported "HR"."JOBS" 6.992 KB 19 rows . . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows . . exported "HR"."LOCATIONS" 8.273 KB 23 rows . . exported "HR"."REGIONS" 5.476 KB 4 rows . . exported "HR"."TEST_T" 6.796 KB 10 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、创建用户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、导入
SQL> grant read,write on directory dmpdir to hr2;
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 25 rows . . imported "HR2"."DEPARTMENTS" 7.007 KB 27 rows . . imported "HR2"."EMPLOYEES" 16.80 KB 107 rows . . imported "HR2"."JOBS" 6.992 KB 19 rows . . imported "HR2"."JOB_HISTORY" 7.054 KB 10 rows . . imported "HR2"."LOCATIONS" 8.273 KB 23 rows . . imported "HR2"."REGIONS" 5.476 KB 4 rows . . imported "HR2"."TEST_T" 6.796 KB 10 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表示从用户hr导入到用户hr2。
三、踩的坑
1）如果10g数据库报错：
ORA-39006: internal error
ORA-39213: Metadata processing is notavailable
先执行：
SQL> execute dbms_metadata_util.load_stylesheets;
2）如果只有ORA-39006: internal error，检查下磁盘是否满了
3）如果遇到：
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
检查create or replace directory dmpdir这句目录名称是否写错
4）报错：
ORA-31626: job does not exist
ORA-31633: unable to create master table “POSP.MY_JOB”
ORA-06512: at “SYS.DBMS_SYS_ERROR”, line 95
ORA-06512: at “SYS.KUPV$FT”, line 1038
ORA-00955: name is already used by an existing object
是因为新的job_name被一个已经停止了的datapump job所占用
解决办法：
清理旧job
select OWNER_NAME, JOB_NAME, OPERATION, JOB_MODE, STATE, ATTACHED_SESSIONS from dba_datapump_jobs;
drop table posp.my_job;
5）报错
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at “SYS.DBMS_LOB”, line 724
ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3964
ORA-06512: at line 1
解决办法：
第一步：select tablespace_name,status from dba_tablespaces;
看到临时表空间都是online状态
第二步：select tablespace_name,file_name from dba_temp_files;
提示no rows selected
第三步：重新添加临时文件
alter tablespace temp add tempfile ‘/oradata/posp/temp01.dbf’;
Tablespace altered.
第四步：检验
select FILE_NAME,TABLESPACE_NAME,STATUS from dba_temp_files;