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;

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

SQL>

本文转自 koumm 51CTO博客，原文链接：http://blog.51cto.com/koumm/1574472，如需转载请自行联系原作者

Oracle expdp/impdp常用示例整理

1. 创建测试数据

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

3. 导出导入全库

(1) 全库导出

(2) 全库导入

4. 导出导入用户

(1) 同用户导入导出

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

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

(4) 导出表

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

(6) 传输表空间

5. 跨版本导入问题

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

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

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

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

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

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

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

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

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

热门文章

最新文章

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle expdp/impdp常用示例整理

1. 创建测试数据

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

3. 导出导入全库

(1) 全库导出

(2) 全库导入

4. 导出导入用户

(1) 同用户导入导出

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

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

(4) 导出表

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

(6) 传输表空间

5. 跨版本导入问题

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

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

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

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

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

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

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

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

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

热门文章

最新文章

相关电子书

推荐镜像