Oracle的导入导出（imp/exp,impdp/expdp)及区别

1.导库前准备工作

新建表空间及用户并赋权

1).数据库管理员登入

su - oracle
sqlplus / as sysdba

2).查看DATAFILE路径

select name from v$datafile;

3).创建表空间（已创建）

【注意】：修改建表空间语句中datafile路径，然后执行脚本

CREATE TABLESPACE USER_DATA
LOGGING
DATAFILE 'datafile路径/USER_DATA.dbf'
SIZE 10G
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE USER_INDEX
LOGGING
DATAFILE 'datafile路径/USER_INDEX.dbf'
SIZE 10G
AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;

4).创建用户（已创建）

create user USER identified by oracle default tablespace USER_DATA temporary tablespace TEMP profile DEFAULT;

5).赋权（已创建）

grant connect,resource to USER;
grant unlimited tablespace to USER;
grant restricted session to USER;
grant exp_full_database to USER;
grant imp_full_database to USER;

--退出
quit

2.导库

考虑是用exp/imp还是expdp/impdp

0).exp/imp及expdp/impdp区别：

a.端使用的区别
EXP 和 IMP 是客户段工具程序，它们既可以在可以客户端使用，也可以在服务端使用。
EXPDP 和 IMPDP 是服务端的工具程序，他们只能在 ORACLE 服务端使用，不能在客户端使用。
expdp/impdp 是服务端程序，影响它速度的只有磁盘 IO。
exp/imp 可以在服务端，也可以在客户端。所以它受限于网络和磁盘。

b.其他参数类区别
数据泵（expdp/impdp）提供如下参数方便进行数据文件、表空间、schema、table等的映射。若有以上映射需求考虑使用数据泵（expdp/impdp）进行导入导出。
• 使用 REMAP_DATAFILE 重新映射数据文件：在具有不同文件系统语义的平台之间移动数据库时，REMAP_DATAFILE 十分有用
• 使用 REMAP_TABLESPACE 重新映射表空间：使用 REMAP_TABLESPACE 可将对象从一个表空间移至另一个表空间
• 使用 REMAP_SCHEMA 重新映射方案：REMAP_SCHEMA 提供原有的 FROMUSER/TOUSER 功能，可用于更改对象所有权
• 使用 REMAP_TABLE 重新映射表：REMAP_TABLE 可用于重命名整个表
• 使用 REMAP_DATA 重新映射数据：REMAP_DATA 可用于在插入数据时重新映射数据

1).exp/imp

exp导出举例：

exp 用户/密码@数据库实例名 owner=用户 buffer=1000000 file=/路径/dmp文件.dmp compress=n rows=y

参数说明：
BUFFER 数据缓冲区的大小
FILE 输出文件 (EXPDAT.DMP)
COMPRESS 默认的EXP时, compress = Y, 也就是把所有的数据压缩在一个数据块上.导入时, 如果不存在连续一个大数据块, 则会导入失败.导出80M以上的大表时, 记得compress= N, 则不会引起这种错误.
ROWS 是否导出表数据行。

imp导入举例（下面示例采用第1步生产库准备工作时建立的用户密码）：

imp USER/oracle@实例  file='/路径/dmp名称.dmp' fromuser=用户 touser=USER ignore=y rows=y;

参数说明：
IGNORE 数据库对象已经存在, 按缺省的imp参数, 则会导入失败。如果用了参数ignore=y, 会把exp文件内的数据内容导入，如果表有唯一关键字的约束条件, 不合条件将不被导入，如果表没有唯一关键字的约束条件, 将引起记录重复。
ROWS 是否导入行。如它为‘N’，那么在导入文件中执行数据库对象的DDL
FROMUSER 导出对象所有者用户名列表
TOUSER 导入对象所有者用户列表

2).expdp/impdp

需要用到逻辑目录
1).若没有目录则需要创建目录，并授权。

示例：

mkdir -p /home/oracle/dmp
cd /home/oracle/dmp
chmod 777 user

2).查询建立的目录是否存在

SQL> select * from dba_directories where directory_name='DATA_DIR';

【说明】：

• 若存在该目录，那么直接使用该目录即可，expdp导出的文件将生成在该目录对应路径下，impdp时需要将对应的dmp文件拷贝至该逻辑目录对应路径下
• 若不存在该目录，那么按如下第3）步示例创建逻辑目录

3).创建逻辑目录(若没有目录/home/oracle/dmp需先创建目录）

SQL> create directory data_dir as '/home/oracle/dmp';

4).用sys管理员给你的指定用户赋予在该目录的操作权限。

grant read,write on directory data_dir to 用户名;

expdp导出

--导出全库，FULL=Y 全库导出，导出的是非 sys 和 system 用户的对象
expdp 用户/密码@数据库实例 dumpfile=名称.dmp directory=DATA_DIR full=y;
--导出指定schema
expdp 用户/密码@数据库实例 dumpfile=名称.dmp directory=DATA_DIR schemas=用户名;

impdp导入

impdp USER/oracle@kode remap_tablespace=fromTablespace:toTablespace REMAP_SCHEMA=fromUser:toUser
full=y directory=data_dir dumpfile='USER_20220708_expdp.dmp'

说明：
a.transform=oid:n 生成新的oid防止冲突
b.remap_tablespace 重新映射表空间 fromTablespace=dmp文件的表空间，toTablespace=要导入的数据库的表空间
c.REMAP_SCHEMA 重新映射用户 fromUser=dmp文件的用户，toUser=要导入的数据库的用户
d.查询逻辑目录信息DATA_DIR：select * from dba_directories where directory_name=‘DATA_DIR’;

3.其他检查项目

1）字符集检查：

exp导出字符集及导入数据库字符集检查，客户端字符集检查；

• 数据库server端字符集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

• dmp文件字符集

16进制查看dmp文件，看第2第3个字节的内容

SQL> select nls_charset_name(to_number('0369','xxxx')) from dual;

NLS_CHARSET_NAME(TO_NUMBER('0369','XXXX'
----------------------------------------
AL32UTF8

【说明】

• exp/imp导出导入时需要关注导入导出的数据库字符集，导入导出客户端字符集（NLS_LANG参数）

  建议导出导入时将客户端的NLS_LANG参数设置成一样的

  --设置NLS_LANG参数
  [oracle@rabbitmq swapdf_dmp]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
  --查看设置的NLS_LANG参数
  [oracle@rabbitmq swapdf_dmp]$ echo $NLS_LANG
  AMERICAN_AMERICA.AL32UTF8
  

• expdp/impdp导出导入时仅关注导入导出的数据库字符集

  在 expdp 的时候 Oracle 不会依赖和参考 NLS_LANG 的设置，而是完全按照数据库本身的字符集导出数据，impdp 的时候，Oracle 会自动判断如果 dmp 文件中的字符集和目标数据库的字符集不符时会自动对导入数据的字符集做转换。