使用sql*loader是大型项目中数据迁移的利器。如果是外部系统，其他数据库到oracle的数据迁移，使用文本式文件是最兼容的方式。
sqlldr的加载效率是很高的，同时在oracle 10g以后推出的oracle_loader效率也不容小视。
sqlldr提供了额外的功能来生成external_table的创建和insert脚本,不过control file是关键，今天尝试的时候就出现了一些问题。
> sqlldr n1/n1 control=NAME_DATA_sqlldr.ctl external_table=generate_only
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Jul 17 08:50:19 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-805: CONTINUEIF not supported by External Table
这个问题在MOS上也没有查到针对性的解决方案，就是提示不支持。
查看control file的内容，如下：
> less NAME_DATA_sqlldr.ctl
LOAD DATA
CONTINUEIF LAST != "|"
INTO TABLE NAME_DATA
APPEND
FIELDS TERMINATED BY '__XXX__' OPTIONALLY ENCLOSED BY '"_"'
TRAILING NULLCOLS
(
NAME_ID INTEGER EXTERNAL NULLIF (NAME_ID=BLANKS)
, SYS_CREATION_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (SYS_CREATION_DATE=BLANKS)
。。。。。
现在的问题就是使用continueif导致了对于外部表不支持。
生成的数据文件中，每行都是以|结尾
因为在oracle_loader中有records teminated by 的选项，可以尝试先生成external table的脚本，然后在脚本中把“records teminated by newline" 修改为”records terminated by '|'"
修改control file为如下的方式，就是删除了continueif的语句
LOAD DATA
INTO TABLE NAME_DATA
APPEND
FIELDS TERMINATED BY '__DOX__' OPTIONALLY ENCLOSED BY '"_"'
TRAILING NULLCOLS
(
NAME_ID INTEGER EXTERNAL NULLIF (NAME_ID=BLANKS)
。。。
然后在生成的日志文件中找到了如下的一段内容,简单修改了表明
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/dbccbsPT1/oracle/NFTCUS1/export/MIG_SCRIPTS_TRUE/CM2_LOG'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE NAME_DATA_EXT_TEST
(
"NAME_ID" NUMBER(10),
。。。。。
"CONV_RUN_NO" NUMBER(3)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'a.bad'
LOGFILE 'a.log_xt'
READSIZE 1048576
现在需要做的就是修改NEWLINE为 ’|’
然后尝试创建。就没有问题了。
SQL> select count(*)from name_data_ext_test;
COUNT(*)
----------
9355
sqlldr的加载效率是很高的，同时在oracle 10g以后推出的oracle_loader效率也不容小视。
sqlldr提供了额外的功能来生成external_table的创建和insert脚本,不过control file是关键，今天尝试的时候就出现了一些问题。
> sqlldr n1/n1 control=NAME_DATA_sqlldr.ctl external_table=generate_only
SQL*Loader: Release 11.2.0.2.0 - Production on Thu Jul 17 08:50:19 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-805: CONTINUEIF not supported by External Table
这个问题在MOS上也没有查到针对性的解决方案，就是提示不支持。
查看control file的内容，如下：
> less NAME_DATA_sqlldr.ctl
LOAD DATA
CONTINUEIF LAST != "|"
INTO TABLE NAME_DATA
APPEND
FIELDS TERMINATED BY '__XXX__' OPTIONALLY ENCLOSED BY '"_"'
TRAILING NULLCOLS
(
NAME_ID INTEGER EXTERNAL NULLIF (NAME_ID=BLANKS)
, SYS_CREATION_DATE DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (SYS_CREATION_DATE=BLANKS)
。。。。。
现在的问题就是使用continueif导致了对于外部表不支持。
生成的数据文件中，每行都是以|结尾
因为在oracle_loader中有records teminated by 的选项，可以尝试先生成external table的脚本，然后在脚本中把“records teminated by newline" 修改为”records terminated by '|'"
修改control file为如下的方式，就是删除了continueif的语句
LOAD DATA
INTO TABLE NAME_DATA
APPEND
FIELDS TERMINATED BY '__DOX__' OPTIONALLY ENCLOSED BY '"_"'
TRAILING NULLCOLS
(
NAME_ID INTEGER EXTERNAL NULLIF (NAME_ID=BLANKS)
。。。
然后在生成的日志文件中找到了如下的一段内容,简单修改了表明
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/dbccbsPT1/oracle/NFTCUS1/export/MIG_SCRIPTS_TRUE/CM2_LOG'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE NAME_DATA_EXT_TEST
(
"NAME_ID" NUMBER(10),
。。。。。
"CONV_RUN_NO" NUMBER(3)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'a.bad'
LOGFILE 'a.log_xt'
READSIZE 1048576
现在需要做的就是修改NEWLINE为 ’|’
然后尝试创建。就没有问题了。
SQL> select count(*)from name_data_ext_test;
COUNT(*)
----------
9355
