oracle导入导出数据

1.如何将oracle数据导出到文本文件(.txt)：

通常我们在不同的表间进行数据复制或迁移会用到以下几种方式：

1)用pl/sql developer导出insert脚本，批量执行

2)建立数据库间的DBLINK,用create table A as select * from A@dblink; 或insert into A select * from A@dblink

3)利用exp,expdp 再imp,impdp 实现

4)Sql Loader(sqlldr)导入数据

第一种方式：insert 时数据库会记录重作日志，产生大量的redo,通常需要在建表是不记录日志文件，不产生redo 来提高insert into 的性能：

create table brian_t(id number(10),

name varchar2(50),

career varchar2(50),

locate varchar2(100)

)nologging;

Insert /* +append */ into brian_t values (111,’brian’,’IT’,’dalian’);

2.Oracle Sql Loader的用法：

Sql Loader导入的数据文件可以是CSV 文件或者以其他分割符分隔的，数据文件的导出方式：

1)用PL/SQL Developer 或者Toad 导出

2)用SQL *Plus 的spool 格式化产出

3)UTL_FILE 包生成

4)用DCBA的工具ociuldr转储数据到数据文件中

5)oracle erp里利用FORM的text_io导出数据到txt文件

(一)用SQL *Plus 的spool 格式化产出

a)建立导出数据的SQL

conn ODS/ODS@syyb165

set echo off

set term off

set linesize 1000

set pagesize 0

set feedback off

set heading off

set trimspool on

spool E:\ab01.txt

select pany_id || '|' || a.insur_kind || '|' ||

to_char(a.insur_date, 'yyyy-mm-dd hh24:mi:ss') || '|' ||

a.insur_status || '|'||to_char(pany_first_insur_date,'yyyy-mm-dd hh24:mi:ss')||'|'||

a.proportion_type ||'|'||a.via_person||'|'||to_char(a.via_date,'yyyy-mm-dd hh24:mi:ss')

from ods_unit_insur_t a;

spool off

/

b)用SQL Loader导入数据文件

建立控制文件exp_data.ctl如下：

LOAD DATA

INFILE 'E:\ab02.txt'

replace INTO TABLE scott.AB02

FIELDS TERMINATED BY"|" OPTIONALLY ENCLOSED BY'"'

Trailing nullcols

(AAB001,AAE140,AAB050 date 'yyyy-mm-dd hh24:mi:ss',AAB051,AAB131 date 'yyyy-mm-dd

hh24:mi:ss',AAA040,AAE011,AAE036 date 'yyyy-mm-dd hh24:mi:ss')

执行：sqlldr scott/tiger@orcl control=E:\exp_data.ctl log=E:\exp_data.log

建立一个控制文件 users.ctl，内容如下：

01.OPTIONS(skip=1,rows=128) --sqlldr命令显示的选项可以写到这里边来,skip=1用来跳过数据中的第一行

02.LOAD DATA

03.INFILE "users_data.csv" --指定外部数据文件，可以写多个INFILE "another_data_file.csv" 指定多个数据文件

04.--这里还可以使用BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，

05.truncate --操作类型，用truncate table 来清除表中原有记录

06.INTO TABLE users -- 要插入记录的表

07.Fields terminated by "," -- 数据中每行记录用"," 分隔

08.Optionally enclosed by '"' -- 数据中每个字段用'"' 框起，比如字段中有"," 分隔符时

09.trailing nullcols --表的字段没有对应的值时允许为空

10.( virtual_column FILLER, --这是一个虚拟字段，用来跳过由PL/SQL Developer 生成的第一列序号

user_id number, --字段可以指定类型，否则认为是CHARACTER 类型, log 文件中有显示

user_name,

login_times,

last_login DATE "YYYY-MM-DD HH24:MI:SS" --指定接受日期的格式，相当用to_date() 函数转换

16.)

说明：在操作类型 truncate 位置可用以下中的一值：

1) insert --为缺省方式，在数据装载开始时要求表为空

2) append --在表中追加新记录

3) replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录

4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

3) 执行命令：

sqlldr dbuser/dbpass@dbservice control=users.ctl

在 dbservice 指示的数据库的表 users 中记录就和数据文件中的一样了。

执行完 sqlldr 后希望能留意一下生成的几个文件，如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件，从中可让你更好的理解 Sql Loader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。

并行加载：sqlldr psbc/psbc control=/opt/impora/t1.ctl parallel=true &

sqlldr psbc/psbc control=/opt/impora/t2.ctl parallel=true &

sqlldr psbc/psbc control=/opt/impora/t3.ctl parallel=true &

sqlldr psbc/psbc control=/opt/impora/t4.ctl parallel=true &

如果并行加载还是很慢，直接灌入磁盘，加入direct参数