Oracle expdp/impdp常用示例整理-白红宇

Oracle expdp/impdp常用示例整理

阅读量：5836 次

发布时间：2019-06-18

本文共 7673 字，大约阅读时间需要 25 分钟。

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

传输表空间具体过程：

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>

转载于:https://blog.51cto.com/koumm/1574472

你可能感兴趣的文章

netty-当一个客户端连接到来的时候发生了什么

查看>>

java socket编程实例代码讲解

查看>>

PHP_5.3.20 源码编译安装PHP-FPM

查看>>

动态代理解释-JDK,CGLIB,JAVASSIST,ASM

No module named 'apt_pkg' 出错

查看>>

shell 练习

查看>>

Debian9.2 下使用vnstat查看服务器带宽流量统计

查看>>

NGINX + PHP-FPM 502

查看>>

Windows Server 2012 之DHCP服务器的备份，还原及转移

查看>>

oracle linux 5.7 布署ogg v11 oracle to oracle之环境配置及数据初始化