Oracle 数据的导入导出（数据泵&EXPDP&IMPDP、python脚本&sqlldr）

数据泵导出导入

1. EXPDP导出数据：
   将数据库对象的表、用户方案、表空间、空间数据导出到转储文件中。
   EXPDP是服务端工具，客户端无法使用。
2. IMPDP导入工具：
   将转储文件中的元数据及其数据导入到Oracle数据中

优点：

• 实现逻辑备份和逻辑恢复
  （参考《Oracle备份与恢复介绍（物理备份与逻辑备份）》：
  逻辑备份：是利用SQL语言从数据库中抽取数据并存于二进制文件的过程。）
  
• 可在数据库用户之间、数据库之间、表空间移动对象

EXPDP导出

1）由于只能将导出的转储文件放在DIRECTORY对象对应的OD目录中，而不能指定。所以需要先建立DIRECTORY对象，并且具有使用DIRECTORY对象的权限。
2） 导出其他用户方案中的表，需要有DBA或者EXP_FULL_DATABASE角色
3） 一次只能导出一个方案中的表

实例：
创建一个DIRECTORY对象，并授予SCOTT使用权限

#  d:\app\dump  目录是需要提前建好的
# 这里的 create directory只是建立逻辑对象
SQL> create directory dump_dir as 'd:\app\dump';
SQL> grant read,write on directory dump_dir to scott;

导出表

实例：
导出scott用户的emp和dept表到转出文件tab.dmp

D:\app>expdp scott/password directory=dump_dir dumpfile=tab.dmp tables=emp,dept

导出方案中的对象

• 用户需具有DBA、EXP_FULL_DATABASE角色

实例：
将scott，hr方案中的所有对象存储到文件schema.dmp中

D:\app>expdp scott/password directory=dump_dir dumpfile=schema.dmp schemas=scott,hr

导出表空间

• 导出表空间是指将一个或多个表空间中的所有对象即数据存储到转储文件中；
• 用户需具有DBA、EXP_FULL_DATABASE角色
• 创建表空间 tbsp_1 ：create TABLESPACE TBSP_1 DATAFILE 'D:\app\Administrator\oradata\oracle\tbsp_1.dbf' SIZE 10M;
  实例：
  通过EXPDP导出表空间tbsp_1;

D:\app>expdp scott/password  directory=dump_dir dumpfile=tablespace.dmp schemas=tbsp_1

导出整个数据库

• 即将数据库的所有对象及数据存储到转储文件中
• 用户必须具有DBA或EXP_FULL_DATABASE角色
• 导出数据库不会导出SYS 、ORDSYS、ORDPLUGINS、CTXSYS、MDSYS、LBACSYS、以及XDB等方案中的对象

实例：
导出整个数据库

D:\app>expdp scott/password directory=dump_dir dumpfile=fulldatabase.dmp full=y

导出对象中的数据

content参数，默认值为 ALL

• all：导出对象定义及其所有数据
• data_only：只导出对象数据
• metadata_only：只导出对象定义

EXPDP HELP 命令，可了解其各个参数的信息

D:\app>expdp scott/password directory=dump_dir dumpfile=content.dmp content=data_only

条件导出

query参数，指定过滤导出数据的where条件
语法：

query=[schema.][table_name:]query_clause

• schema：指定方案名
• table_name：指定表名
• query_caluse：制定条件限制语句
  —该参数不能与content=metadata_only extimate_only、transport_tablespaces等参数同时使用

实例：
通过QUERY参数过滤导出dept表中部门编号为10的数据

D:\app>expdp scott/password directory=dump_dir dumpfile=query.dump tables=emp query='where deptno=10 '

IMPDP数据导入

dump_dir 需要在此用户下创建好：

SQL> create directory dump_dir as 'd:\app\dump';

导入表

• 导入表是指：将存放在转储文件中的一个或多个表的结构及数据装载到数据库中
• 以其他用户导入表，用户需具有IMP_FULL_DATABASE和DBA角色，
• 导入其他schema（方案）时，指定REMAP_SHEMA参数

remap_schema=scott:sys：将源schema—scott的所有对象 装载到目标schema—sys的目标方案中
实例：
将Scott的dept、emp表导入到SYSTEM方案中

D:\app>impdp  "'sys/password@oracle as sysdba'" directory=dump_dir dumpfile=tab.dmp tables=scott.emp remap_schema=scott:sys

用户角色验证时，由于sys是dba用户，所以不能简单地用用户名和密码登陆
正确语法是：sys/password@oracle as sysdba
这样才不会出现以下报错：

DI-28009: 操作产生了 ORACLE 错误 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

用户名:
UDI-00005: 读取输入值时出现意外的文件结尾。

导入表空间所有对象

即将 存放在转储文件中的一个或多个表空间中的所有对象装载到数据库中。
参数：tablespace

实例：
将tbsp_1表空间中的所有对象都导入到当前的数据库中

D:\app>impdp  "'sys/password@oracle as sysdba'" directory=dump_dir dumpfile=tablespace.dmp tablespace=tbsp_1

导入全数据库

即将 存放在转储文件中的所有数据库对象及相关数据装载到数据库中。
参数：full

实例：
从 fulldatabase.dmp 文件中导入全数据库

D:\app>impdp  "'sys/password@oracle as sysdba'" directory=dump_dir dumpfile=fulldatabase.dmp full=y

移动表空间时导入数据文件

IMPDP参数：

1. REMAP_SCHEMA
=SOURCE_SCHEMA:TARGET_SCHEMA
   将源方案中所有对象转载到目标方案中;
2. REMAP_TABLESPACE
=SOURCE_TABLESPACE:TARGET_TABLESPACE
   指定导入时更改表空间名称;
3. SQLFILE
=[DIRECTORY_OBJECT:]FILE_NAME
   FILE_NAME表示包含DDL语句的文件，IMPDP只是从DMP文件中提取对象的DDL语句写入到SQL脚本，并不导入数据；
4. TABLE_EXISTS_ACTION
={SKIP|APPEND|TRUNCATE|REPLACE}
   用于指定当表已经存在时导入作业要执行的操作，默认为SKIP
   • skip：跳过已存在表处理下一对象
   • append：追加数据
   • truncate：截断表，追加新数据
   • replace：删除已存在表，重建后追加新数据
5. TRANSPORT_DATAFILES
=DATAFILE_NAME
   指定被复制到目标数据库的数据文件

EXCEL文件导入Oracle（python脚本导入）

详情参照《EXCEL文件导入Oracle（python脚本导入）
》