在升级项目中,经常需要对数据进行迁移,我这次主要操作的是将数据从Access迁移到Oracle中.如何将数据导入Oracle数据库中,我总结了两种方法,供参考.
1.SQL loader
1.1 主要特征
SQL loader是Oracle数据库自带的,通过命令行操作的数据加载工具,主要有以下特征:
- 在同一加载会话中从多个数据文件加载数据。
- 在同一加载会话期间将数据放入多个表中。
- 指定数据的字符集。
- 有选择地加载数据。
- 在指定的列中生成唯一的顺序键值。
- 生成详细的错误报告,这大大有助于故障排除。
- 使用常规或直接路径加载。 尽管常规路径加载非常灵活,但是直接路径加载提供了卓越的加载性能。
1.2 结构概览
以下是SQL loader的概览.
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200628224152829.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTIyODUyNg==,size_16,color_FFFFFF,t_70)
input datafiles:数据文件,提供需要导入Oracle的数据
loader control file :控制文件,对数据加载的动作进行描述
log file:log文件,记录log
bad file: 坏文件,记录由于错误而被拒绝的行,这些错误可能包括错误的数据类型或参照完整性约束
discard file:丢弃文件,记录由于SQL * Loader控制文件中的语句而被过滤掉而被丢弃的行。
1.3 典型例子
1 -- This is a sample control file
2 LOAD DATA
3 INFILE 'sample.dat'
4 BADFILE 'sample.bad'
5 DISCARDFILE 'sample.dsc'
6 APPEND
7 INTO TABLE emp
8 WHEN (57) = '.'
9 TRAILING NULLCOLS
10 (hiredate SYSDATE,
11 deptno POSITION(1:2) INTEGER EXTERNAL(2)
NULLIF deptno=BLANKS,
12 job POSITION(7:14) CHAR TERMINATED BY WHITESPACE
NULLIF job=BLANKS "UPPER(:job)",
mgr POSITION(28:31) INTEGER EXTERNAL
TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
ename POSITION(34:41) CHAR
TERMINATED BY WHITESPACE "UPPER(:ename)",
empno POSITION(45) INTEGER EXTERNAL
TERMINATED BY WHITESPACE,
sal POSITION(51) CHAR TERMINATED BY WHITESPACE
"TO_NUMBER(:sal,'$99,999.99')",
13 comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%'
":comm * 100"
)
以下为说明性注释:
- 控制文件中写注释的方法;
- LOAD DATA语句告诉SQL * Loader这是新数据加载的开始;
- INFILE语句指定包含要加载的数据的数据文件的名称;
- BADFILE语句指定放置拒绝记录的文件的名称;
- DISCARDFILE语句指定放置废弃记录的文件的名称;
- APPEND语句是将数据加载到非空表中时可以使用的选项之一,可以根据需要用REPLACE,TRUNCATE替代。使用INSERT语句是将数据加载到空表中的选项;
- INTO TABLE子句允许标识表,字段和数据类型;
- WHEN子句指定一个或多个字段条件;
- TRAILING NULLCOLS子句告诉SQL * Loader将记录中不存在的相对定位的列视为空列;
10.SYSDATE将该列设置为当前系统日期 ;
11.POSITION指定数据字段的位置;NTEGER EXTERNAL是该字段的数据类型;NULLIF子句是可用于指定字段条件的子句之一;
12.TERMINATED BY WHITESPACE子句是可以为字段指定的分隔符之一;
13.ENCLOSED BY子句是另一个可能的字段定界符
2.PLSQL developer
2.1 text importer
通过这个工具工具可以将txt文件导入数据库,性能较好.
其中有几个我踩过的坑或心得:
- 点击“导入”按钮之后,出现“没有右括号”的报错.Oracle数据库会对数据进行一定的处理.我原来的数据是字符串类型的,Oracle会在SQL function中,使用to_date()函数对用于表示日期的字符串进行处理,这里会错误地多加引号,导致报错.由于我仍然要使用字符串类型的数据,所以我把SQL function清空了,数据就正常导入了.
- 准备的txt文件中有换行符,导致Oracle不能正确识别对应行.将TXT文件放进notepa++中,首先调整notepa++的设置,是换行符LF得以表示;然后使用转义字符进行替换.
- 在这个画面上,可以将txt文件做成一份SQL脚本.
2.2 import table
这个工具可以读SQL脚本导入数据,但性能较差.
以上只是提供了思路,具体使用方法,请参见互联网.