Oracle数据导出与导入

最新推荐文章于 2021-07-28 15:20:24 发布

原创最新推荐文章于 2021-07-28 15:20:24 发布 · 952 阅读

1 ·

CC 4.0 BY-SA版权

数据库相关专栏收录该内容

25 篇文章

订阅专栏

本文详细介绍了如何使用SQL*Plus的spool命令将Oracle数据库中的数据导出到文件，包括处理汉字乱码问题，以及通过SQL*Loader进行数据文件的导入操作，涉及ctl文件配置和异常处理。同时，讲解了SQL*Plus中一些常用设置参数的作用。

一、数据导出成文件（使用spool）

1、创建 toout.sql 文件

下面是文件内容的代码，名称和地址可以替换成自己实际的

conn 用户名/密码
set echo off
set term off
set pagesize 0; 
set linesize 200;
set feedback off
set heading off
set trimspool on 
spool /a/b/c/file.txt （这是要生成的文件）
select 字段A||'|'||字段B||'|'||字段C||'|' from 表;
spool off
set trimspool off
set heading on
set feedback on
set term on
set echo on
exit

2、将 toout.sql 放到数据库服务器某目录，并修改toout.sql文件权限为可执行

3、进入该目录并执行 toout.sql文件

执行方法：

1、进入文件所在目录后，连接sqlplus：

sqlplus /nolog

2、执行sql文件：

start toout.sql

如图

执行完就在你指定的目录生成文件了

4、异常情况

1、spool导出数据时，生成的txt或csv文件中，汉字是一堆问号或乱码。一般这种情况就是数据库与服务器字符集不同。

查询数据库字符集

select userenv('language') from dual;

查询linux字符集

echo $LANG
和
echo $NLS_LANG

如：

我的数据库字符集是AMERICAN_AMERICA.ZHS32GB18030

服务器LANG字符集是en_US:zh_CN.GB18030

服务器没有NLS_LANG字符集

解决办法：

我们可以临时修改字符集（不用担心改错，因为仅本次登录有效，关掉页面重进就恢复原来的了）

正常应该修改NLS_LANG为AMERICAN_AMERICA.ZHS32GB18030，也就是跟数据库相同，但我测试之后，输出文件汉字还是乱码。

因此我使用了其他字符集尝试，并发现下面这个字符集可以让导出的文件是汉字。

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

语句的使用方法是：在连接sqlplus之前执行该语句（该语句会临时修改服务器字符集，且仅本次连接有效，退出或关掉页面就恢复原来的了）

2、用户权限不足，执行不了

换用户

sudo - oracle

5、话外语（set解释）

spool本身其实没有啥难的 ，就是set参数的个数太太多啦！具体不知道啥意思，就直接百度查，找想要的自己改，下面是一部分我碰到的

SQL>set newp none  //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的

SQL>set echo off; //显示start启动的脚本中的每个sql命令，缺省为on

SQL> set echo on               //设置运行命令是是否显示语句

SQL> set feedback on;       //设置显示“已选择XX行”

SQL>set feedback off;      //回显本次sql命令处理的记录条数，缺省为on即去掉最后的 "已经选择10000行"

SQL>set heading off;    //输出域标题，缺省为on 设置为off就去掉了select结果的字段名，只显示数据

SQL>set headsep off  //标题分隔符

SQL>set pagesize 0;     //输出每页行数，缺省为24,为了避免分页，可设定为0。（可以简写为：set pages 0）

SQL>set linesize 80;     //输出一行字符个数，缺省为80。（可以简写为：set line 80）

SQL>set numwidth 12;     //输出number类型域长度，缺省为10

SQL>set termout/term off;    //显示脚本中的命令的执行结果，缺省为on

SQL>set trimout on; //去除标准输出每行的拖尾空格，缺省为off

SQL>set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off

SQL>set serveroutput on;  //设置允许显示输出类似dbms_output

SQL> set timing on;          //设置显示“已用时间：XXXX”

二、文件内容导入数据库

1、写个ctl文件,如toin.ctl

下面是文件内容的代码，名称和地址可以替换成自己实际的

load data

CHARACTERSET AL32UTF8               --（在ctl文件加上字符集，与oracle的字符集相同即可 select * from v$nls_parameters t where t.PARAMETER =' NLS_CHARACTERSET';）

infile '/a/b/c/File_A.csv'   --将被导入的文件名

append into table Table_A --将导入数据的表名 append replace 也可以换位append into，一个是追加，一个是替换。

fields terminated by ','   --文件中的分隔符

trailing nullcols     --将所有不在纪录中的指定位置的列当作空值

(  session_id  ,    --对应数据库相应列
   flag,
   line_num,
   content char(4000) --ctl中规定，字段超过256字节需要在ctl中写明字段类型长度。如果不指定，或报错如下：数据文件的字段超出最大长度 记录 1485: 被拒绝 - 表 Table_A 的列 CONTENT 出现错误。
)

2、将 toin.ctl 放到数据库服务器某目录，并修改toin.ctl文件权限为可执行

3、执行ctl文件

linux命令

sqlldr 用户名/密码@实例名 control=/a/b/c/toin.ctl

windows命令

sqlldr userid = 用户名/密码@实例 control = /a/b/c/toin.ctl

4、异常情况

就按照上面代码一般没啥错，要是权限不足，就换oracle用户

sudo - oracle