xmltype oracle imp,oracle expdp和impdp导出导入

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;