ORA-12899 – Value too large for column string

本文介绍了解决因特殊字符导致Oracle数据库导入失败的方法。通过调整列长度语义、使用特定脚本进行转换,并逐步导出和导入数据来避免错误12899的发生。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

有空再翻译吧

If we need to recover a scheme which default LANG is WE8ISO8859P1 and our database is AL32UTF8, the import process crash because the special characters during the conversion pass from one byte to two:

Column 12 LA APLICACION EN ESPA?A DEL CONVENIO DE LA HAYA DE...
IMP-00019: row Rejected due to Oracle error 12899
IMP-00003: ORACLE Error 12899 Encountered
ORA-12899: value too large for column "SCHEME". "TABLE". "ROW" (actual: 51, maximum: 50)

To resolved this problem we’ll need changing columns to CHAR length semantics, following this steps:

  • Export the original database
  • Import only the table definitions into the new database, without inserting the rows (ROWS=N import)
  • Converts columns to CHAR length semantics
  • Import the rows

Export

Export the scheme to the directory MY_BCK, ensure that MY_BCK exists:

SQL> CREATE OR REPLACE DIRECTORY MY_BCK AS '/u01/app/oracle/bck/';

Run the export with SYSTEM user, and datapump utility:

SQL> expdp system/password schemas=SCOTT directory=MY_BCK dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Or with conventional export system:

SQL> exp system/password owner=SCOTT file=/u01/app/oracle/bck/SCOTT.dmp log=/u01/app/oracle/bck/expdpSCOTT.log

Table definitions

Import only the definitions of the tables, no the rows

SQL> impdp system/password schemas=SCOTT directory=MY_BCK dumpfile=SCOTT.dmp logfile=impdpSCOTT.log content=metadata_only
SQL> imp system/password fromuser=SCOTT touser=SCOTT file=/u01/app/oracle/bck/SCOTT.dmp log=/u01/app/oracle/bck/expdpSCOTT.log ignore=Y ROWS=N

CHAR conversion

To facilitate this process, Oracle created this script. You only need to edit the name of your scheme:

Conn / as sysdba
set feedback off
set verify off
set serveroutput on
set termout on
exec dbms_output.put_line('Starting build select of columns to be altered');
drop table semantics$
/
create table semantics$(s_owner varchar2(40),
s_table_name varchar2(40),
s_column_name varchar2(40),
s_data_type varchar2(40),
s_char_length number)
/
insert into semantics$
select C.owner, C.table_name, C.column_name, C.data_type, C.char_length
from all_tab_columns C, all_tables T
where C.owner = T.owner
and T.owner in
('SCOTT')
-- All Oracle provided users
and C.table_name = T.table_name
and C.char_used = 'B'
-- only need to look for tables who are not yet CHAR semantics.
and T.partitioned != 'YES'
-- exclude partitioned tables
and C.table_name not in (select table_name from all_external_tables)
and C.data_type in ('VARCHAR2', 'CHAR')
-- You can exclude or include tables or shema's as you wish, by adjusting
-- "and T.owner not in" as per your requirements
/
commit
/
declare
cursor c1 is select * from semantics$;
v_statement varchar2(255);
v_nc number(10);
v_nt number(10);
begin
execute immediate
'select count(*) from semantics$' into v_nc;
execute immediate
'select count(distinct s_table_name) from semantics$' into v_nt;
dbms_output.put_line
('ALTERing ' || v_nc || ' columns in ' || v_nt || ' tables');
for r1 in c1 loop
v_statement := 'ALTER TABLE ' || r1.s_owner || '.' || r1.s_table_name;
v_statement := v_statement || ' modify (' || r1.s_column_name || ' ';
v_statement := v_statement || r1.s_data_type || '(' || r1.s_char_length;
v_statement := v_statement || ' CHAR))';
execute immediate v_statement;
end loop;
dbms_output.put_line('Done');
end;
/

Recompile the scheme to validate dependent objects:

SQL> EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);

Import Data

To finish importing the data scheme

SQL> impdp system/password schemas=SCOTT directory=MY_BCK dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
SQL> imp system/password fromuser=SCOTT touser=SCOTT file=/u01/app/oracle/bck/SCOTT.dmp log=/u01/app/oracle/bck/expdpSCOTT.log ignore=Y

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ INSERT /*+ APPEND */ INTO TP_0082994_234655828 NOLOGGING (RN,LOTNAME,N_A2300_AVG_TOTALDEFECTCO,N_A3300_AVG_TOTALDEFECTCO,N_A4300_AVG_TOTALDEFECTCO,N_A9950_AVG_TOTALDEFECTCO) SELECT ROWNUM AS RN, LOTNAME,N_A2300_AVG_TOTALDEFECTCO,N_A3300_AVG_TOTALDEFECTCO,N_A4300_AVG_TOTALDEFECTCO,N_A9950_AVG_TOTALDEFECTCO FROM ( SELECT LOTNAME,MAX(N_A2300_AVG_TOTALDEFECTCO) AS N_A2300_AVG_TOTALDEFECTCO,MAX(N_A3300_AVG_TOTALDEFECTCO) AS N_A3300_AVG_TOTALDEFECTCO,MAX(N_A4300_AVG_TOTALDEFECTCO) AS N_A4300_AVG_TOTALDEFECTCO,MAX(N_A9950_AVG_TOTALDEFECTCO) AS N_A9950_AVG_TOTALDEFECTCO FROM ( SELECT LOTNAME, PROCESSOPERATIONNAME, (CASE WHEN PROCESSOPERATIONNAME='A2300' THEN MAX(AVG_TOTALDEFECTCOUNT||','||AVG_O_SIZE_DEFECT_COUNT) END) AS N_A2300_AVG_TOTALDEFECTCO, (CASE WHEN PROCESSOPERATIONNAME='A3300' THEN MAX(AVG_TOTALDEFECTCOUNT||','||AVG_O_SIZE_DEFECT_COUNT) END) AS N_A3300_AVG_TOTALDEFECTCO, (CASE WHEN PROCESSOPERATIONNAME='A4300' THEN MAX(AVG_TOTALDEFECTCOUNT||','||AVG_O_SIZE_DEFECT_COUNT) END) AS N_A4300_AVG_TOTALDEFECTCO, (CASE WHEN PROCESSOPERATIONNAME='A9950' THEN MAX(AVG_TOTALDEFECTCOUNT||','||AVG_O_SIZE_DEFECT_COUNT) END) AS N_A9950_AVG_TOTALDEFECTCO FROM TP_0082994_234026762 WHERE 1=1 AND PROCESSOPERATIONNAME in ('A2300','A3300','A4300','A9950') GROUP BY LOTNAME,PROCESSOPERATIONNAME ) GROUP BY LOTNAME ) ]; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "YMSMGR"."TP_0082994_234655828"."N_A9950_AVG_TOTALDEFECTCO" (actual: 43, maximum: 41) ; nested exception is java.sql.SQLException: ORA-12899: value too large for column "YMSMGR"."TP_0082994_234655828"."N_A9950_AVG_TOTALDEFECTCO" (actual: 43, maximum: 41)
08-01
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [ INSERT /*+ APPEND */ INTO TP_0082994_235948067 NOLOGGING (RN,LOTNAME,N_A2300_AVG_TOTALDEFECT_A,N_A3300_AVG_TOTALDEFECT_A,N_A4300_AVG_TOTALDEFECT_A,N_A9950_AVG_TOTALDEFECT_A) SELECT ROWNUM AS RN, LOTNAME,N_A2300_AVG_TOTALDEFECT_A,N_A3300_AVG_TOTALDEFECT_A,N_A4300_AVG_TOTALDEFECT_A,N_A9950_AVG_TOTALDEFECT_A FROM ( SELECT LOTNAME,MIN(N_A2300_AVG_TOTALDEFECT_A) AS N_A2300_AVG_TOTALDEFECT_A,MIN(N_A3300_AVG_TOTALDEFECT_A) AS N_A3300_AVG_TOTALDEFECT_A,MIN(N_A4300_AVG_TOTALDEFECT_A) AS N_A4300_AVG_TOTALDEFECT_A,MIN(N_A9950_AVG_TOTALDEFECT_A) AS N_A9950_AVG_TOTALDEFECT_A FROM ( SELECT LOTNAME, PROCESSOPERATIONNAME, (CASE WHEN PROCESSOPERATIONNAME='A2300' THEN MIN(AVG_TOTALDEFECT||','||AVG_O_SIZE_DEFECT||','||AVG_L_SIZE_DEFECT) END) AS N_A2300_AVG_TOTALDEFECT_A, (CASE WHEN PROCESSOPERATIONNAME='A3300' THEN MIN(AVG_TOTALDEFECT||','||AVG_O_SIZE_DEFECT||','||AVG_L_SIZE_DEFECT) END) AS N_A3300_AVG_TOTALDEFECT_A, (CASE WHEN PROCESSOPERATIONNAME='A4300' THEN MIN(AVG_TOTALDEFECT||','||AVG_O_SIZE_DEFECT||','||AVG_L_SIZE_DEFECT) END) AS N_A4300_AVG_TOTALDEFECT_A, (CASE WHEN PROCESSOPERATIONNAME='A9950' THEN MIN(AVG_TOTALDEFECT||','||AVG_O_SIZE_DEFECT||','||AVG_L_SIZE_DEFECT) END) AS N_A9950_AVG_TOTALDEFECT_A FROM TP_0082994_235905268 WHERE 1=1 AND PROCESSOPERATIONNAME in ('A2300','A3300','A4300','A9950') GROUP BY LOTNAME,PROCESSOPERATIONNAME ) GROUP BY LOTNAME ) ]; SQL state [72000]; error code [12899]; ORA-12899: value too large for column "YMSMGR"."TP_0082994_235948067"."N_A9950_AVG_TOTALDEFECT_A" (actual: 64, maximum: 61) ; nested exception is java.sql.SQLException: ORA-12899: value too large for column "YMSMGR"."TP_0082994_235948067"."N_A9950_AVG_TOTALDEFECT_A" (actual: 64, maximum: 61)
最新发布
08-02
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值