Oracle Database 导入导出

1.1  Oracle Database数据导出常用的有EXP工具，但是此工具在导出大数据量的时候效率较低，所以在10g后推出DATAPUMP（数据泵）此工具的效率比EXP高很多。

1.2   背景

1. 导入/导出（Exp/Imp）是Oracle幸存的最古老的两个命令行工具，但导入/导出（Exp/Imp）并不是一种好的备份方式，正确的说法是Exp/Imp只能是一个好的转储工具，特别是在小型数据库的转储，表空间的迁移，表的抽取，检测逻辑和物理冲突等中有不小的功劳。我们也可以把它作为小型数据库的物理备份后的一个逻辑辅助备份，也是不错的建议。对于越来越大的数据库，特别是TB级数据库和越来越多数据仓库的出现，EXP/IMP越来越力不从心了。
2. Oracle Database 10g开始推出的数据泵EXPDP/IMPDP，提高了导入导出的性能，也增加了导入导出的功能。数据泵是一个基于服务器端的高速导入导出工具，通过dbms_datapump包来调用  提供expdp，impdp，以及基于Web页面来实现导入导出。 提供两种数据数据方式：直接路径、外部表。可以定制数据泵作业，以及从作业中分离和重新附加到作业

1.3   区别

1. EXP/IMP是客户端程序，它既可以在客户端使用，也可以在服务端使用。
2. EXPDP/IMPDP是服务端程序，他们只能在ORACLE服务端使用，不能在客户端使用。
3. IMP只适用于EXP导出的文件，IMPDP只适用与EXPDP导出的文件，两者互不兼容。
4. EXPDP/IMPDP只适用于Oracle Database 10g及以上版本。EXP/IMP都可以使用。
5. EXPDP/IMPDP包括导出表，导出方案，导出表空间，导出数据库4种方式。

1.4   术语解释

序号

术语

解释

范例

1.

EXP/IMP

导入/导出工具

2

USERID

用户名/口令  

• hec2dev/hec2dev

3

FULL  

导出整个文件 (N)

• FULL=Y

4

OWNER

所有者用户名列表

• Owner=hec2dev

5

FILE

输出文件 (EXPDAT.DMP)

• file=D:/oracle/oracle_dump/hec2dev1101.dmp

6

LOG  

屏幕输出的日志文件

• log=hec2dev_yyyymmdd.log

7

BUFFER

数据缓冲区大小

• BUFFER=64000

8

ROWS

 导出数据行 (Y)

• rows=y

9

TABLESPACES 

要导出的表空间列表

• tables=(hec2dev)

2．

数据泵

Oracle数据库中导入/导入工具DATAPUMP的中文简称。在oracle10g诞生

3.

EXPDP/IMPDP

DATAPUMP中执行导出/导入操作的工具

4.

DIRECTORY

Oracle数据库中的一种对象类型，操作系统路径在数据库中的一种映射。

• create or replace directory DUMP_DIR as '/u01/oracle/oracle_dump';

5.

DUMPFILE

用于指定转储文件的名称,默认名称为expdat.dmp

• DUMPFILE=[directory_object:]file_name [,….]
• directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_object,导出工具会自动使用directory

6.

LOGFILE

指定导出日志文件文件的名称,默认名称为export.log

• LOGFILE=[directory_object:]file_name

7.

SCHEMA

该方案用于指定执行方案模式导出,默认为当前用户方案

• schema=${user}

8

TABLESPACES

指定要导出表空间列表

• CREATE TABLESPACE test DATAFILE '/u01/oradata/mastest/test01.dbf' SIZE 50M  AUTOEXTEND ON ;

9

VERSION

指定被导出对象的数据库版本,默认值为COMPATIBLE.

• version='10.2.0.1.0'

10

DATAFILE

存放表空间的物理地址

 

2.     EXP/IMP

1. 目标：

目前在10.1.2.38服务器上已经存在一个hec2dev的数据库作为源数据库，在10.1.2.39服务器上的hec2dev的数据库作为目标数据库，作为备份。

2. 当前案例信息
   1. 数据库管理帐号：oracle
   2. 数据文件存放地址

导出源数据文件地址：             /u01/oracle/oracle_dump/hec2dev1101.dmp

预期目标数据文件地址：/u01/oracle/oracle_dump/hec2dev1101.dmp

1. 数据库ORACLE_HOME

源数据库ORACLE_HOME：             /u01/oracle/10gdb

预期目标数据库ORACLE_HOME:     /u01/oracle/10gdb

.

2.1 登录源数据库

1. telnet登陆源服务器

命令格式： telnet  <至少一个空格>  <目标IP地址>

当前示例：telnet  10.1.2.38

1. 以root帐号登陆

输入用户名root和密码

例如： login: root<回车>

root's Password:handhand<回车>

1. 启动数据库

/root/startdb.sh

1. 切换到目标用户下（通常oracle帐号管理数据库，oraias帐号管理IAS服务器）

  su –   oracle

1. 创建dump目录

 

[oracle@hand ~]$cd /u01/app/orcle

[oracle@hand ~]$mkdir oracle_dump

2.2  导出EXP

1. 导出数据文件到指定目录（目录名称自己定义，只要自己能找到就行，和用户名没有关系

 

[oracle@hand ~]$cd /u01/app/orcle/oracle_dump

[oracle@hand~]exp hec2dev/hec2dev file = hec2dev140701.dmp owner= hec2dev 

 

有三种主要的方式（完全、用户、表） 

1. 完全： 

[oracle@hand ~]$cd /u01/app/orcle/oracle_dump

[oracle@hand~]exp hec2dev/hec2dev file= hec2dev140701.dmp full=y

 

如果要执行完全导出，必须具有特殊的权限 

2. 用户模式：

[oracle@hand ~]$cd /u01/app/orcle/oracle_dump

[oracle@hand~]exp hec2dev/hec2dev buffer=64000 file = hec2dev140701.dmp owner= hec2dev 

 

这样用户hec2dev的所有对象被输出到文件中。 

3. 表模式： 

[oracle@hand ~]$cd /u01/app/orcle/oracle_dump

[oracle@hand~]exp hec2dev/hec2dev buffer=64000 file = hec2dev140701.dmp owner= hec2dev tables=( hec2dev)

 

2. 登录数据库

[oracle@localhost root]$ sqlplus

User：hec2dev

Password ：hec2dev

3. 查看用户默认表空间，以便导入时创建一样的表空间

SQL>select username,default_tablespace from dba_users where username = ‘hec2dev’;

4. 查看用户使用的表空间

SQL>select DISTINCT owner ,tablespace_name from dba_extents where owner like ‘hec2dev’;

5. 查看表空间对应的数据文件，以便在目标数据库上创建大小合适的数据文件  。

default_tablespace为默认表空间的名称tablespace_name为用户使用表空间的名称

SQL>select file_name,tablespace_name from dba_data_files where tablespace_name in (default_tablespace, tablespace_name); 

 

.2.3  导入IMP

1. telnet登陆目标服务器

命令格式： telnet  <至少一个空格>  <目标IP地址>

当前示例：telnet  10.1.2.39

2. 以root帐号登陆

输入用户名root和密码

例如： login: root<回车>

root's Password:handhand<回车>

3. 启动数据库

/root/startdb.sh

4. 切换到目标用户下（通常oracle帐号管理数据库）

  su –   oracle

5. 登录数据库

使用sys登录查看目标用户、数据库文件、表空间是否存在，如果不存在，需要创建相关对象

[oracle@hand ~]$sqlplus “/as sysdba”

 

6. 看是否存在源数据库的表空间，如果查找不到，说明没有这个两个表空间，需要创建  。

SQL>select name from v$tablespace where name in (default_tablespace, tablespace_name);  

7. 创建表空间

CREATE TABLESPACE hec2dev DATAFILE '/u01/app/oracle/oradata/mas/hec2dev.dbf' SIZE 50M

 AUTOEXTEND ON ; 

8. 创建用户。

SQL> create user hec2dev identified by hec2dev default tablespace hec2dev;  

 

9. 用户授权。

SQL> grant connect to hec2dev;

grant dba to hec2dev;

grant resource to hec2dev;

grant unlimited tablespace to hec2dev;

 

10. 创建临时表空间：

CREATE temporary tablespace hec2dev_temp tempfile='/u01/app/oracle/oradata/mas/hec2dev_temp.dbf'

SIZE 50m AUTOEXTEND ON;

11. 导入数据：

回到本机，打开CMD

[oracle@hand ~]$cd /u01/app/orcle/oracle_dump

[oracle@hand~]imp hec2dev/hec2dev fromuser=hec2dev touser=hec2dev file =hec2dev140701.dmp ignore=y

12. 导入可能出现的问题：
13. imp和exp使用的字符集不同

      如果字符集不同, 导入会失败, 可以改变unix环境变量或者NT注册表里NLS_LANG相关信息.导入完成后再改回来. 

2. imp和exp版本不能往上兼容

imp可以成功导入低版本exp生成的文件, 不能导入高版本exp生成的文件

3   EXPDB与IMPDB

1. 目标：

目前在10.12.38服务器上已经存在一个hec2dev的数据库作为源数据库，在10.1.2.38服务器上的hec2train的数据库作为目标数据库，作为备份。

2. 当前案例信息
   1. 数据库管理帐号：oracle
   2. 数据文件存放地址Directory

源数据文件地址：            '/u01/oracle/oracle_dump'（服务器）

预期目标数据文件地址：'/u01/oracle/oracle_dump'（服务器）

1. 数据库ORACLE_HOME

源数据库ORACLE_HOME：             /u01/oracle/10gdb

预期目标数据库ORACLE_HOME:     /u01/oracle/10gdb

 

查找oracle的安装路径：echo $ORACLE_HOME;

1. telnet登陆目标服务器

命令格式： telnet  <至少一个空格>  <目标IP地址>

当前示例：telnet  10.1.2.39

1. 以root帐号登陆

输入用户名root和密码

例如： login: root<回车>

root's Password:handhand<回车>

1. 启动数据库

/root/startdb.sh

切换到目标用户下（通常oracle帐号管理数据库，oraias帐号管理IAS服务器）

  su –   oracle

1. 登录数据库

[oracle@localhost root]$ sqlplus

User：hec2dev

Password ：hec2dev

 

 3.1   创建Directory

指定转储文件和日志文件所在的目录

Directory=directory_object

Directory_object用于指定目录对象名称.需要注意,目录对象是使用CREATE Directory语句建立的对象,而不是OS目录（此命令需要dba权限，例如:在mastest数据库中，需要以masdemo用户身份登陆.）

- Create Directory

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>create or replace directory DUMP_DIR as '/u01/app/oracle/oracle_dump';

3.2   授予权限

为数据库用户HEC2DEV授予使用DIRECTORY对象的权限，最好以system等管理员赋予

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>grant read,write on directory DUMP_DIR to hec2dev;

3.3   导出

用su – oracle命令切换到oracle用户下面，执行下面的语句, schema为当前用户

[oracle@hand ~]$cd /u01/app/orcle/oracle_dump

expdp hec2dev/hec2dev directory=DUMP_DIR dumpfile= hec2dev140701.dmp  logfile=DUMP_DIR:hec2dev140701.log 

备注：

1. directory= DUMP_DIR必须放在前面，如果将其放置最后，会提示 ORA-39002: 操作无效

 ORA-39070: 无法打开日志文件。

 ORA-39087: 目录名 DATA_PUMP_DIR; 无效 

2. 导出语句后面不要有分号
3. 创建的目录一定要在数据库所在的机器上。

解决方法：将编码方式由utf-8改为简体中文。

可能出现的问题：

1. 指定的Net服务名不正确。

解决方法：因为没有配置指定ORACLE_SID，可以用命令：echo $ORACLE_SID进行查看是否指定了ORACLE_SID，若没有，可以通过命令:export ORACLE_SID=mastest(要导出数据库的sid)解决。

（2）中文乱码。

解决方法：将编码方式由utf-8改为简体中文

（3）无法打开日志文件。

  解决方法：这是因为由create or replace directory DUMP_DIR as '/u01/app/oracle/oracle_dump';这个命令创建的oracle_dump文件夹的user和group均为root,应该改为：oracle和dba.故可以先切换到root用户下，再调用命令：chown oracle:dba oracle_test;可以将oracle_test的用户和组改为oracle和dba.

3.4   创建表空间

1. 登录数据库

[oracle@localhost root]$ sqlplus

User：hec2train

Password ：hec2train

 

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>CREATE TABLESPACE hec2train_tablespace DATAFILE  ‘DUMP_DIR/hec2dev.dbf’ SIZE 50M

 AUTOEXTEND ON ; 

创建临时表空间：

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>create temporary tablespace hec2train_temp

tempfile ' DUMP_DIR/hec2dev.dbf'

size 50m AUTOEXTEND ON;

创建物理表空间：

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>create tablespace hec2train_data

logging

datafile ' DUMP_DIR/hec2train_data.dbf'

size 50m AUTOEXTEND ON;

删除表空间：

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

3.5   创建用户

1. 删除 test用户以及这个用户下的所有对象；

 

 

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>drop user dev cascade;

2. 创建用户dev 密码也为dev默认的表空间为dev

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>create user dev identified by test default tablespace dev;

（create user username identified by password default tablespace

tablespace_name ）

3.6   授予权限

1. 授予给dev的用户connect权限

拥有connect权限的用户只可以登录Oracle

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>grant connect to dev

但这个时候dev还是不能登陆成功的，我们需要赋予相应的权限

2. 赋予alter session的权限

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>grant alter session to dev;

grant create any context to dev;

3. 授予创建存储过程的权限

 

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>grant create procedure to dev;

4. 授予创建序列的权限

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>grant create sequence to dev;

5. 授予创建会话的权限

[oracle@hand ~]$sqlplus “/as sysdba”

SQL>grant create session to dev;

6. 授予创建同义名的权限

grant create synonym to dev;

7. 授予创建表的权限

grant create table to dev;

8. 授予创建类型的权限

grant create type to dev;

9. 授予创建用户的权限

grant create user to dev;

10. 授予创建视图的权限

grant create view to dev;

11. 授予为任意用户创建表的权限

grant create any table to dev;

12. 授予调试(比如存储过程的test)的权限

grant debug connect session to dev;

13. 授予查询重写的权限

grant query rewrite to dev;

14. 授予查询数据字典的权限

grant select any dictionary to dev;

15. 授予操作表空间大小不加限制的权限

grant unlimited tablespace to dev;

16. 授予使用DIRECTORY对象的权限

grant read,write on directory DUMP_DIR to dev;

注意：

如何查找目录  select * from dba_directories;

3.7   导入

impdp hec2train / hec2train remap_tablespace=hec2dev:hec2train_tablespace remap_schema=hec2dev:dev

directory=DUMP_DIR dumpfile=hec2dev1101.dmp

logfile=DUMP_DIR:hec2train1101_imp.log

可能出现的问题：

1. 路径错误。注意directory=DUMP_DIR的路径及其logfile的路径。
2. 要注意remap_tablespace=hec2dev: hec2train _tablespace，hec2dev是导出时的数据库的表空间，hec2train _tablespace是后来自己创建的表空间（第5步），如果当时不知道导出时数据库的表空间，执行一下该导入语句，报错了也就知道了。

3.8   扩展

导出类型:

1. 按用户导

expdp hec2dev/hec2dev schemas=dev directory=DUMP_DIR dumpfile=hec2dev1101.dmp

2. 按表名导

expdp hec2dev/hec2dev TABLES=emp,dept directory=DUMP_DIR dumpfile=hec2dev1101.dmp;

3. 按查询条件导

expdp hec2dev/hec2dev directory=DUMP_DIR dumpfile=hec2dev1101.dmp Tables=emp query='WHERE deptno=20';

4. 按表空间导

expdp hec2dev/hec2dev directory=DUMP_DIR dumpfile=hec2dev1101.dmp TABLESPACES=temp,example;

5. 导整个数据库

expdp hec2dev/hec2dev directory=DUMP_DIR dumpfile=hec2dev1101.dmp FULL=y;

6. 带版本号导出（从高版本导出，导入到低版本时需要带版本号,点击pl/sql的命令窗口或者sqlplus hec2dev/hec2dev@mastest，在最上面都会有版本号）：

expdp hec2dev/hec2dev directory= DUMP_DIR dumpfile=hec2dev1101.dmp logfile=DUMP_DIR:hec2dev1101.log schemas=dev version='10.2.0.1.0'