Oracle数据导入

方案一：使用Oracle自带的SQL*LOADER工具。SQL*LOADER是Oracle的数据加载工具，通常用来将操作系统上的文件导入到Oracle数据库表中。SQL*LOADER使用.ctl控制文件来描述数据文件各记录、各字段与数据库表结构之间的对应关系，以控制数据的正确导入。在UNIX下使用SQL*LOADER的示例：

 

假设有数据文件data.cvs【数据文件可以是任何约定的格式，只要在ctl中写明了解析这种格式的方式】

20110001,1101,Oracle

20110002,1101,Mysql

20110003,1102,DB2

20110004,1103,SQL Server

 

数据库表

DBStudent(no char(8),class char(4),name varchar(30))

 

控制文件load.ctl

load        data

infile       'data.csv'

insert      into  table       DBStudent

fields      terminated      by    ',' TRAILING NULLCOLS

(no，class，name)

 

在命令行输入

sqlldr username/password@tns_name control='load.ctl' log=log.txt bad=bad.txt

 

data.csv的数据成功导入到表DBStudent中。关于sqlldr参数：

username        数据库用户名

password        数据库密码

tns_name        数据库TNS名称（若数据库在本机上，可以不写）

control           ctl控制文件

log                 LOG文件，记录导入过程的所有信息，包括导入记录数、Error等。

bad                存放导入失败的数据。如果所有数据导入成功，则该文件不存在。

更多sqlldr和ctl文件的参数待续。。。。。。

 

使用SQL*LOADER导入数据失败或数据乱码的常见原因：

1. 数据文件错误。如存在多条数据格式不一致、中文字符编码、字段为空、文件路径错误等。

2. CTL文件错误。如没有指定一条记录的长度，导致数据混乱；没有指明空字段如何处理（TRAILING NULLCOLS）等。

3. 其他错误。如没有数据库表的读写权限。

 

 

方案二：使用Oracle自带的imp工具。exp/imp是Oracle自带的一对数据导出/导入工具，常用于数据恢复与备份，使用exp/imp工具可以实现单表导入、用户导入、数据库导入三个级别的导入操作。exp/imp分交互式、非交互式两种处理方式。交互式就是一步一步Next，非交互式就是一条命令搞定。使用交互式imp的示例：

$ imp

Import: Release 8.1.6.0.0 - Production on 星期五 12月 7 17:01:08 2001

(c) Copyright 1999 Oracle Corporation.   All rights reserved.

用户名:  test

口令:****

连接到: Oracle8i Enterprise Edition Release 8.1.6.0.0 - 64bit Production

With the Partitioning option

JServer Release 8.1.6.0.0 - Production

导入文件: expdat.dmp> /tmp/m.dmp

输入插入缓冲区大小（最小为 8192 ) 30720>

经由常规路径导出由EXPORT:V08.01.06创建的文件

警告: 此对象由 TEST 导出，而不是当前用户

已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入

只列出导入文件的内容(yes/no)：no>

由于对象已存在，忽略创建错误(yes/no)：no> yes

导入权限(yes/no)：yes>

导入表数据(yes/no)：yes>

导入整个导出文件(yes/no)：no> yes

. 正在将TEST的对象导入到 SCOTT

. . 正在导入表                        "CMAMENU"        4336行被导入

成功终止导入，但出现警告。

 

使用非交互式imp的示例：

$ imp system/manager fromuser=jones tables=(accts)

$ imp system/manager fromuser=scott tables=(emp,dept)

$ imp system/manager fromuser=scott touser=joe tables=emp

$ imp scott/tiger file = expdat.dmp full=y

$ imp scott/tiger file = /mnt1/t1.dmp show=n buffer=2048000 ignore=n commit=y grants=y full=y

log=/oracle_backup/log/imp_scott.log

$ imp system/manager parfile=params.dat

params.dat 内容

file=dba.dmp show=n ignore=n grants=y fromuser=scott tables=(dept,emp)

 

使用导入工具imp可能出现的问题

(1) 数据库对象已经存在

一般情况，导入数据前应该彻底删除目标数据下的表，序列，函数/过程，触发器等； 数据库对象已经存在，按缺省的imp参数，则会导入失败；如果用了参数ignore=y，会把exp文件内的数据内容导入。如果表有唯一关键字的约束条件，不合条件将不被导入；如果表没有唯一关键字的约束条件，将引起记录重复。

 

(2) 数据库对象有主外键约束

不符合主外键约束时，数据会导入失败。

解决办法：

①先导入主表，再导入依存表。

②disable目标导入对象的主外键约束，导入数据后，再enable它们。

 

(3)权限不够

如果要把A用户的数据导入B用户下，A用户需要有imp_full_database权限。

 

(4)导入大表( 大于80M ) 时，存储分配失败。

默认的EXP时，compress = Y，也就是把所有的数据压缩在一个数据块上。导入时，如果不存在连续一个大数据块，则会导入失败。导出80M以上的大表时，记得compress= N，则不会引起这种错误。

 

(5) imp和exp使用的字符集不同

如果字符集不同，导入会失败，可以改变unix环境变量或者NT注册表里NLS_LANG相关信息，导入完成后再改回来。

 

(6) imp和exp版本不能往上兼容

imp可以成功导入低版本exp生成的文件，不能导入高版本exp生成的文件。根据情况我们可以用

$ imp username/password@connect_string

说明: connect_string 是在/ORACLE_HOME/network/admin/tnsnames.ora

定义的本地或者远端数据库的名称

注意事项:

UNIX：/etc/hosts            要定义本地或者远端数据库服务器的主机名

Windows：windows\hosts              和IP地址的对应关系

 

方案三：使用可视化工具TOAD，PL/SQL ，SQL Developer，SQL Navigator等等。