oracle RAC 11g sqlload 生产表导入数据（ORA-12899）

背景：由于即将来临的双十一，业务部门（我司是做京东，天猫的短信服务），短信入库慢，需要DBA把数据sqlload进数据库。

表结构如下：

MRS VARCHAR2(100),
STATUS VARCHAR2(8),
STATUSTJ VARCHAR2(8),
MMDD VARCHAR2(6),
SN VARCHAR2(20),
ROUTEID NUMBER(4),
RETURNTIME DATE,
RECEIVETIME DATE,
SUBMITTIME DATE,
MTYPE NUMBER(1),
SRSPAN NUMBER,
SPCODE VARCHAR2(21),
BANCHROUTE NUMBER(4),
PROVINCE VARCHAR2(20),
CITY VARCHAR2(20),
USERID NUMBER(10),
SCODE VARCHAR2(25),
UUID VARCHAR2(40)

 

编写控制文件：

LOAD DATA
INFILE '/u01/app/oracle/utl/daodata/rrpt/rrp_181108.txt'
APPEND
INTO TABLE S_T_RTNRP_STATUS
FIELDS TERMINATED BY ','  trailing nullcols
(
MRS,
STATUS,
STATUSTJ,
MMDD,
SN,
RETURNTIME date "YYYY-MM-DD hh24:mi:ss",
RECEIVETIME date "YYYY-MM-DD hh24:mi:ss",
ROUTEID,
SUBMITTIME date "YYYY-MM-DD hh24:mi:ss",
MTYPE,
SRSPAN,
SPCODE,
BANCHROUTE,
PROVINCE,
CITY,
USERID,
SCODE,
UUID
)

 --注意：

trailing nullcols表示列可以为空，如果新建的表列content有大对象，控制文件里面列后面 content char(1000000)这样。

sqlldr XXX/XXX@db75 control=SqlLdr2.ctl

报错如下：

 

 报错类似如下：

ORA-12899: value too large for column "SMART"."S_T_RTNRP_STATUS"."SCODE" (actual: 32, maximum: 25)

后面修改了表的字段，有原先varchar(20)改成varchar(50)还是报错（由于操作时间，未截图）。

 

怀疑是字符集的问题，经查实，操作系统的字符集是UTF8，而数据库的字符集是16gbk,导入的PROVINCE, CITY,字段为中文（如下图），后面重新的应用导出数据，不要这两个字段。

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET')

重新修改控制文件：

LOAD DATA
INFILE '/u01/app/oracle/utl/daodata/rrpt/rrp_181108.txt'
APPEND
INTO TABLE S_T_RTNRP_STATUS
FIELDS TERMINATED BY ',' trailing nullcols
(
MRS,
STATUS,
STATUSTJ,
MMDD,
SN,
RETURNTIME date "YYYY-MM-DD hh24:mi:ss",
RECEIVETIME date "YYYY-MM-DD hh24:mi:ss",
ROUTEID,
SUBMITTIME date "YYYY-MM-DD hh24:mi:ss",
MTYPE,
SRSPAN,
SPCODE,
BANCHROUTE,
USERID,
SCODE,
UUID
)

 

export LANG=16GBK
export nls_lang="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"

sqlldr xxx/xxx  control=SqlLdr2.ctl

后成功导入30多万条记录。

 

大对象的案例：

如文件：

 

 

创建一张有clob 的表：

create table TEST
(
id VARCHAR2(4000),
rpt_date VARCHAR2(4000),
platform_ident VARCHAR2(4000),
sys_game_level_code_id VARCHAR2(4000),
normal_parlay VARCHAR2(4000),
account_code VARCHAR2(4000),
user_info_id INTEGER,
play_type VARCHAR2(4000),
bet_no VARCHAR2(4000),
bet_time VARCHAR2(4000),
odds_type VARCHAR2(4000),
wager_odds VARCHAR2(4000),
currency_code VARCHAR2(4000),
wager_stake VARCHAR2(4000),
wager_stake_1 VARCHAR2(4000),
win_amt VARCHAR2(4000),
win_amt_1 VARCHAR2(4000),
betting_status VARCHAR2(4000),
last_update VARCHAR2(4000),
match_date VARCHAR2(4000),
final_stake VARCHAR2(4000),
final_stakel VARCHAR2(4000),
bet_detail_json CLOB,
final_status VARCHAR2(4000),
batch_no VARCHAR2(4000),
data_sign VARCHAR2(4000),
code VARCHAR2(4000),
betting_status_text VARCHAR2(4000),
settlement_status_text VARCHAR2(4000),
validbet_amt VARCHAR2(4000),
sport_bet_type_text VARCHAR2(4000)
)；

控制文件

cat SqlLdr2.ctl
LOAD DATA
INFILE '/home/oracle/9.txt'
APPEND
INTO TABLE test
FIELDS TERMINATED BY '|$$$$$|' trailing nullcols
(
ID ,
RPT_DATE ,
PLATFORM_IDENT ,
SYS_GAME_LEVEL_CODE_ID ,
NORMAL_PARLAY ,
ACCOUNT_CODE ,
USER_INFO_ID ,
PLAY_TYPE ,
BET_NO ,
BET_TIME ,
ODDS_TYPE ,
WAGER_ODDS ,
CURRENCY_CODE ,
WAGER_STAKE ,
WAGER_STAKE_1 ,
WIN_AMT ,
WIN_AMT_1 ,
BETTING_STATUS ,
LAST_UPDATE ,
MATCH_DATE ,
FINAL_STAKE ,
FINAL_STAKEL ,
BET_DETAIL_JSON char(1000000),
FINAL_STATUS ,
BATCH_NO ,
DATA_SIGN ,
CODE ,
BETTING_STATUS_TEXT ,
SETTLEMENT_STATUS_TEXT ,
VALIDBET_AMT ,
SPORT_BET_TYPE_TEXT
)

export nls_lang=AMERICAN_AMERICA.AL32UTF8

sqlldr racttfc/oracle@test control=SqlLdr2.ctl

 

 

导入成功后查看test 内容。