Oracle数据的导入与导出

本文针对window操作系统与oracle12C的版本。

1.sqlplus执行单个sql文件

1.执行sqlplus登陆命令：sqlplus username/password@host:port/service_name as sysdba （其中普通用户可以不加后面的 as sysdba）

2.在sqlplus下执行sql文件命令：@file_path

 

2.sqlplus执行多个sql文件

1.新建一个sql文件，在其添加需执行的sql文件路径：

@file_path1

@file_path2

。。。。。

2.在sqlplus下执行新建的sql文件即可

 

3.oracle导出与导入工具expdp，impdp

使用expdp和impdp时应该注意的事项：

exp和imp是客户端工具程序，它们既可以在客户端使用，也可以在服务端使用。

expdp和impdp是服务端的工具程序，他们只能在oracle服务端使用，不能在客户端使用。

imp只适用于exp导出的文件，不适用于expdp导出文件；impdp只适用于expdp导出的文件，而不适用于exp导出文件。

1.导出

1.以sysdba身份登陆sqlplus，如命令：sqlplus / as sysdba

2.创建逻辑目录，该命令不会在操作系统创建真正的目录，create or replace directory 目录别名 as '操作系统上目录的绝对路径'；

如：create directory xyy as 'D:\cowry\cj'；

3.查看管理理员目录（同时查看操作系统是否存在，因为oracle并不关心该目录是否存在，如果不存在，后面会出错）

select * from dba_directories;

4.赋予需要导出的用户在指定目录的操作权限。命令：grant read,write on directory 目录别名 to user_name；

5.退出sqlplus，在cmd窗口执行以下命令：

1）导出用户

　expdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp [logfile=xxx.log] directory=xxx [schemas=xxx] [compression=xxx] [parallel=xxx]

 

2)导出指定表

　expdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp directory=xxx tables=xxx,xxx1,...

 

还有其他导出表空间，整个数据库等等模式，具体可查相关资料。

dumpfile：导出的数据文件的名称。

logfile：日志文件

directory：导出的逻辑目录，一定要在oracle中创建完成的，并授权用户读写权限

schemas：使用dblink导出的用户不是本地的用户，需要加上schema来确定导出的用户，类似于exp中的owner，但还有一定的区别。

compression：压缩转储文件。有效的关键字值为: ALL, DATA_ONLY, [METADATA_ONLY] 和 NONE。

parallel：并行数量。

tables：指定导出的表。

network_link：源系统的远程数据库链接的名称。使用的dblink来远程导出，需要指定dblink的名称。

 

2.导入

1）导入用户

　　impdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp [logfile=xxx.log] directory=xxx [remap_schema=xxx] [parallel=xxx]

 

2）导入表

　　impdp user_name/pwd@host:port/service_name dumpfile=xxx.dmp directory=xxx [remap_schema=xxx:xxx1] [parallel=xxx] tables=xxx,xxx1,...

 

remap_schema：将一个用户的的数据迁移到另外一个用户

 

4.oracle使用sqlloader导入批量数据

 

SQL * Loader功能

SQL * Loader将外部文件中的数据加载到Oracle数据库的表中。

它具有强大的数据解析引擎，对数据文件中的数据格式几乎没有限制。您可以使用SQL * Loader执行以下操作：

• 如果数据文件位于与数据库不同的系统上，则通过网络加载数据。

• 在同一个加载会话期间从多个数据文件加载数据。

• 在同一个加载会话期间将数据加载到多个表中。

• 指定数据的字符集。

• 有选择地加载数据（您可以根据记录的值加载记录）。

• 使用SQL函数在加载数据之前处理数据。

• 在指定列中生成唯一的顺序键值。

• 使用操作系统的文件系统访问数据文件。

• 从磁盘，磁带或命名管道加载数据。

• 生成复杂的错误报告，极大地帮助排除故障。

• 加载任意复杂的对象关系数据。

• 使用辅助数据文件来加载LOB和集合。

• 使用传统的，直接的路径或外部表负载。

 

您可以通过两种方式使用SQL * Loader：带或不带控制文件。控制文件控制SQL * Loader的行为以及加载中使用的一个或多个数据文件。使用控制文件可以更好地控制加载操作，这对于更复杂的加载情况可能是理想的。但是对于简单加载，您可以在不指定控制文件的情况下使用SQL * Loader; 这被称为SQL * Loader express模式。

sqlloader参数说明，具体可在cmd输入sqlldr查看

有效的关键字:

    userid -- ORACLE 用户名/口令
   control -- 控制文件名
       log -- 日志文件名
       bad -- 错误文件名
      data -- 数据文件名
   discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目         (全部默认)
      skip -- 要跳过的逻辑记录的数目  (默认 0)
      load -- 要加载的逻辑记录的数目  (全部默认)
    errors -- 允许的错误的数目         (默认 50)
      rows -- 常规路径绑定数组中或直接路径保存数据间的行数
               (默认: 常规路径 64, 所有直接路径)
  bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)
    silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)
    direct -- 使用直接路径                     (默认 FALSE)
   parfile -- 参数文件: 包含参数说明的文件的名称
  parallel -- 执行并行加载                    (默认 FALSE)
      file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)
commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)
  readsize -- 读取缓冲区的大小               (默认 1048576)
external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows -- 直接路径列数组的行数  (默认 5000)
streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)
multithreading -- 在直接路径中使用多线程
 resumable -- 对当前会话启用或禁用可恢复  (默认 FALSE)
resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)
date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)
no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)
partition_memory -- 开始溢出的直接路径分区内存限制 (kb)  (默认 0)
     table -- 用于快速模式加载的表
date_format -- 用于快速模式加载的日期格式
timestamp_format -- 用于快速模式加载的时间戳格式
terminated_by -- 由用于快速模式加载的字符终止
enclosed_by -- 由用于快速模式加载的字符封闭
optionally_enclosed_by -- (可选) 由用于快速模式加载的字符封闭
characterset -- 用于快速模式加载的字符集
degree_of_parallelism -- 用于快速模式加载和外部表加载的并行度
      trim -- 用于快速模式加载和外部表加载的截取类型
       csv -- 用于快速模式加载的 csv 格式数据文件
    nullif -- 用于快速模式加载的表级 nullif 子句
field_names -- 用于快速模式加载的数据文件第一条记录字段名设置
dnfs_enable -- 启用或禁用输入数据文件 Direct NFS (dNFS) 的选项  (默认 FALSE)
dnfs_readbuffers -- Direct NFS (dNFS) 读缓冲区数  (默认 4)
sdf_prefix -- 要附加到每个 LOB 文件和辅助数据文件的开头的前缀
      help -- 显示帮助消息  (默认 FALSE)
empty_lobs_are_null -- 将空白 LOB 设置为空值  (默认 FALSE)
  defaults -- 直接路径默认值加载; EVALUATE_ONCE, EVALUATE_EVERY_ROW, IGNORE, IGNORE_UNSUPPORTED_EVALUATE_ONCE, IGNORE_UNSUPPORTED_EVALUATE_EVERY_ROW
direct_path_lock_wait -- 当前已锁定时, 等待表访问权限  (默认 FALSE)

PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlldr
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'。位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。

 

1.建立控制文件，后缀名为.ctl，在控制文件加入以下内容：

OPTIONS (SKIP=num,ROWS=num,DIRECT=true,BINDSIZE=num)

LOAD DATA

CHARACTERSET 字符集

INFILE "数据文件路径" BADFILE "错误文件路径，其文件后缀为.bad" DISCARDFILE "废弃文件路径，其文件后缀为.dis"

如有多个数据文件继续添加
INFILE "xxx" BADFILE "xxx" DISCARDFILE "xxx"

......

[操作类型] INTO TABLE table_name
fields terminated by "xxx"

optionally enclosed by "xxx"
trailing nullcols
(col，col1，col2，...)

参数说明：

SKIP：跳过开始的行数，即不读取的行数。

 

ROWS：对于传统常规路径导入的情况，代表一次提交的行数。

 

BINDSIZE：每次提交记录的缓冲区的最大值（仅适用于传统常规路径加载），默认256000 Bytes。通过BINDSIZE的设定，要比默认值和通过参数ROWS计算的缓冲区大小更优先。

即BINDSIZE能够制约ROWS，如果ROWS提交的数据需要的缓冲区大于BINDSIZE的设定，会以BINDSIZE的设定为准。

 

DIRECT：使用直接路径(默认FALSE)。

 

CHARACTERSET：通常会出现中文乱码问题。即数据文件的字符集编码与oracle字符集编码不一致导致中文乱码问题。其值有：ZHS16GBK。。。

 

操作类型：

　　insert     --为缺省方式，在数据装载开始时要求表为空
　　append  --在表中追加新记录
　　replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
　　truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

 

fields terminated by：每行字段与字段之间的分隔符。

 

optionally enclosed by：数据中每个字段用 "" 框起，比如字段中有 "," 分隔符时。对于包裹的字段为中文，可能会出现导不进数据库，那么可尝试把源数据文件编码格式修改为ASCII。

 

trailing nullcols：如要导入源文件此列内容为空，在导入到数据库表中，此列内容就是null。

 

2.在cmd窗口执行命令：

　　sqlldr user_name/pwd@service_name control=控制文件路径 log=日志文件路径

 

5.oracle使用UTL_FILE包导出批量数据

文件I/O对于数据库的开发来说显得很重要，比如数据库中的一部分数据来自于磁盘文件，那么就需要使用I/O接口把数据导入到数据库中来。在PL/SQL中没有直接的I/O接口，  

一般在调试程序时可以使用Oracle自带的DBMS_OUTPUT包的put_line函数（即向屏幕进行I/O操作）即可，但是对于磁盘文件的I/O操作它就无能为力了。

其实Oracle同样也提供了可以进行文件I/O的实用包-----UTL_FILE包，利用这个实用包提供的函数来实现对磁盘的I/O操作。

 

1.以sysdba身份创建目录：CREATE OR REPLACE DIRECTORY 目录别称 AS '目录路径';

 

2.把路径的读写权限赋予用户：GRANT READ,WRITE ON DIRECTORY 目录别称 TO 用户名；

 

3.在用户中创建以下存储过程：

create or replace PROCEDURE SQL_TO_FILE(P_QUERY IN VARCHAR2,P_DIR IN VARCHAR2,P_FILENAME IN VARCHAR2)
IS
  L_OUTPUT UTL_FILE.FILE_TYPE; 
  L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
  L_COLUMNVALUE VARCHAR2(4000);
  L_STATUS INTEGER;
  L_COLCNT NUMBER := 0;
  L_SEPARATOR VARCHAR2(1);
  L_DESCTBL DBMS_SQL.DESC_TAB;
  P_MAX_LINESIZE NUMBER := 32000;
BEGIN
  L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
  DBMS_SQL.PARSE(L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(L_THECURSOR, L_COLCNT, L_DESCTBL);
  FOR I IN 1 .. L_COLCNT LOOP
    UTL_FILE.PUT(L_OUTPUT,L_SEPARATOR || '"' || L_DESCTBL(I).COL_NAME || '"');
    DBMS_SQL.DEFINE_COLUMN(L_THECURSOR, I, L_COLUMNVALUE, 4000);
    L_SEPARATOR := ',';
  END LOOP;
  UTL_FILE.NEW_LINE(L_OUTPUT);
  L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
  WHILE (DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0) LOOP
    L_SEPARATOR := '';
    FOR I IN 1 .. L_COLCNT LOOP
      DBMS_SQL.COLUMN_VALUE(L_THECURSOR, I, L_COLUMNVALUE);
      UTL_FILE.PUT(L_OUTPUT,
                  L_SEPARATOR || '"' ||
                  TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE, '"', '""')) || '"');
      L_SEPARATOR := ',';
      END LOOP;
    UTL_FILE.NEW_LINE(L_OUTPUT);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
  UTL_FILE.FCLOSE(L_OUTPUT);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

 

4.执行上述过程

EXEC SQL_TO_FILE('para','para1','para2');

其中第一个参数为：查询数据的SQL，第二个为：目录别称，第三个为：导出的文件名。