Oracle expdp/impdp常用示例整理-阿里云开发者社区

expdp不依赖和参考NLS_LANG的设置，而是完全按照数据库本身的字符集进行转换, 平时数据量不大时一直采用exp/imp导数据库，到了数据量大的时候expdp/impdp就看到效果了，一次exp导出28G的DMP文件用了3个小时，采用EXPDP用了20分钟。

1. 创建测试数据

SQL> create tablespace DATA datafile '/u01/app/oracle/oradata/orcl/DATA.dbf' size 10m autoextend on next 10m;
SQL> create user abc identified by abc default tablespace DATA;
SQL> grant connect,resource to abc;
SQL> conn abc/abc
SQL> create table t as select * from all_objects;
SQL> select * from tab;
TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
T                              TABLE
SQL>

2. 配置directory目录与用户访问权限

SQL> conn / as sysdba;
SQL> create directory dump_dir as '/home/oracle';
SQL> col owner format a5
SQL> col directory_name format a25;
SQL> col directory_path format a50;
SQL> select * from dba_directories;

OWNER DIRECTORY_NAME            DIRECTORY_PATH
----- ------------------------- --------------------------------------------------
SYS   DUMP_DIR                  /home/oracle
SQL>
SQL> grant read,write on directory dump_dir to public;
注：public角色隐式授予每位用户, 数据库中创建的每个账户都有权访问这些权限, 也可以针对用户授权。

删除多余目录配置SQL：

drop directory dump_dir;

3. 导出导入全库

(1) 全库导出

$ expdp \'/ as sysdba\' directory=dump_dir full=y dumpfile=fulldb.dmp parallel=2;

(2) 全库导入

$ impdp \'/ as sysdba\' directory=dump_dir full=y dumpfile=fulldb.dmp parallel=2 table_exists_action=replace;

4. 导出导入用户

(1) 同用户导入导出

$ expdp abc/abc directory=dump_dir dumpfile=abc.dmp

导入示例如下：需要创建abc用户

$ impdp abc/abc directory=dump_dir dumpfile=abc.dmp

(2) 不同用户导入导出,abc用户下数据导入到tom用户下

$ expdp abc/abc directory=dump_dir dumpfile=abc.dmp logfile=data.log

导入示例如下：如果不事先创建tom用户的话，IMPDP会自动创建tom用户，需要用有权限的帐号，本例是采用系统管理员权限。

$ impdp \'/ as sysdba\' directory=dump_dir dumpfile=abc.dmp remap_schema=abc:tom

先创建tom用户的导入方式

$ impdp tom/tom directory=dump_dir dumpfile=abc.dmp remap_schema=abc:tom

(3) 同时导出两个用户的所有表，分别导入，也可以一起导入。

expdp \'/ as sysdba\' directory=dump_dir dumpfile=db_abc_tom.dmp schemas=abc,tom

(4) 导出表

expdp abc/abc directory=dump_dir dumpfile=abc_tables.dmp TABLES=emp,dept

(5) 并行导出，可以加入速度

expdp abc/abc directory=dump_dir dumpfile=full.dmp parallel=40

(6) 传输表空间

expdp \'/ as sysdba \' dumpfile=tjoa.dmp directory=dump_dir transport_tablespaces=TJOA
impdp \'/ as sysdba \' dumpfile=tjoa.dmp directory=dump_dir transport_datafiles=/u01/app/oracle/oradata/orcl/tjoa.dbf remap_schema=tjoa:tjoa

传输表空间具体过程：http://koumm.blog.51cto.com/703525/1574822

5. 跨版本导入问题

低版本是不能导入高版本dmp，需要在高版本的EXPDP导出时指定版本号导出。低版本IMPDP无需指定版本。

例如：11.2.0.4导入到10.2.0.5

expdp abc/abc directory=dump_dir dumpfile=abc_tables.dmp version=10.2.0.5.0

6. 同一数据库不同用户间通过IMPDP实现数据迁移复制

说明：IMPDP工具提供的NETWORK_LINK参数可以实现的用户间的数据复制。

1）确认tnsnames.ora文件中的连接串

$ cat $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = HOSTNAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PLSExtProc)
)
)

2）在数据库系统中创建一个指向自身的DATABASE LINK

语法示例:
CREATE DATABASE LINK 数据库链接名 CONNECT TO 用户名 IDENTIFIED BY 密码 USING '本地配置的数据的实例名';

database link使用方式:
不指定 : 默认值建立一个private的database link, 只有创建它的用户可以使用。
PUBLIC : 连接可以被数据库中的所有的用户访问。

SQL> create public database link dblink_self connect to system identified by oracle using 'ORCL';
SQL>
SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED
--------------- ------------------------- --------------- ------------------------- --------------
PUBLIC DBLINK_SELF SYSTEM ORCL 11-114

删除dblink方式：

SQL> drop public database link DBLINK_SELF;

3) 复制ABC用户的数据到XXX用户

这里事先创建了xxx用户，这里才报的ora-31684，可以不用事先创建用户,impdp会自动创建。

$ impdp system/oracle network_link=dblink_self schemas=abc remap_schema=abc:xxx

Import: Release 10.2.0.5.0 - 64bit Production on 11 112014 22:23:51

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** network_link=dblink_self schemas=abc remap_schema=abc:xxx
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"XXX" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "XXX"."T" 40702 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 22:24:09

查看数据表：
[oracle@node1 ~]$ sqlplus xxx/xxx;

SQL*Plus: Release 10.2.0.5.0 - Production on 11 22:25:00 2014

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE

SQL>

说明：表中字段有LONG类型的字段，该方式network_link不支持，可以采用EXP/EXPDP方式导出再导入方式。
ORA-31679: Table data object "TEST"."LOG_BLOG" has long columns, and longs can not be loaded/unloaded using a network link

7. 不同数据库间通过IMPDP实现数据库用户间迁移复制

说明：IMPDP工具提供的NETWORK_LINK参数可以实现的不同数据库间用户间的数据复制,本例是network_link的又一用法，原理一致，可以在实际环境中灵活使用。

node1(192.168.233.150): orcl : abc 用户
node2(192.168.233.160): orcl : xyz 用户

1）在node2上配置到node1的tnsnames.ora服务名文件

$ cat $ORACLE_HOME/network/admin/tnsnames.ora

ORCL_NODE1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.233.150)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

2）在NODE2数据库系统中创建一个指向自身的DATABASE LINK

语法示例:
CREATE DATABASE LINK 数据库链接名 CONNECT TO 用户名 IDENTIFIED BY 密码 USING '本地配置的数据的实例名';

SQL> create public database link dblink_to_node1 connect to system identified by oracle using 'ORCL_NODE1';
SQL>

3) 复制node1上ABC用户的数据到node2上xyz用户下

这里node2事先创建好abc用户对应的表空间DATA.

SQL> create tablespace DATA datafile '/u01/app/oracle/oradata/orcl/DATA.dbf' size 10m autoextend on next 10m;

[oracle@node2 ~]$ impdp system/oracle network_link=dblink_to_node1 schemas=abc remap_schema=abc:xyz

Import: Release 10.2.0.5.0 - 64bit Production on 11 112014 23:23:08

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** network_link=dblink_to_node1 schemas=abc remap_schema=abc:xyz
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 5 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "XYZ"."T" 40702 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 23:23:21

[oracle@node2 ~]$

说明：这里专门采用通过impdp自动创建用户的方式，故意没有提前创建用户，但是导入之后还需要重新为用户创建密码。

node2查看数据表：
SQL> conn / as sysdba;
Connected.
SQL>
SQL> alter user xyz identified by xyz default tablespace DATA;

User altered.

SQL> conn xyz/xyz;
Connected.
SQL> select * from tab;