ORA-12899 – Value too large for column string
来源:互联网 发布:php join函数 编辑:程序博客网 时间:2024/06/04 19:09
If we need to recover a scheme which default LANG is WE8ISO8859P1 and our database isAL32UTF8, 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 12899IMP-00003: ORACLE Error 12899 EncounteredORA-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
- 1 Export
- 2 Table definitions
- 3 CHAR conversion
- 4 Import Data
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 sysdbaset feedback offset verify offset serveroutput onset termout onexec 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_lengthfrom all_tab_columns C, all_tables Twhere C.owner = T.ownerand T.owner in('SCOTT')-- All Oracle provided usersand C.table_name = T.table_nameand C.char_used = 'B'-- only need to look for tables who are not yet CHAR semantics.and T.partitioned != 'YES'-- exclude partitioned tablesand 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/declarecursor c1 is select * from semantics$;v_statement varchar2(255);v_nc number(10);v_nt number(10);beginexecute 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 loopv_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
- ORA-12899 – Value too large for column string
- ORA-12899: value too large for column
- ORA-12899: value too large for column
- goldengate ORA-12899: value too large for column
- ORA-12899: value too large for...
- ORA-12899: value too large for column "SOAU"."SJQY_QTSBSPEC"."PROPERTY_6" (actual: 566, maximum: 500
- OGG: NLS_LENGTH_SEMANTICS报错信息ora-12899 value too large for column
- Oralce导入数据时提示ORA-12899错误value too large for column
- Oralce导入数据时提示ORA-12899错误value too large for column
- Oracle导数时报错:ORA-12899: value too large for column
- Caused by: com.sap.db.jdbc.exceptions.BatchUpdateExceptionSapDB:inserted value too large for column
- 在windows 下用tools导出导入Repository出错 value too large for column "SIEBEL"."S_ACCELERATOR
- Value too large for defined data type
- gcc 编译错误 Value too large for defined data type
- mysql: Packet for query is too large 和Data too long for column
- mysql: Packet for query is too large 和Data too long for column
- Incorrect string value: '\xABh' for column
- Data for Source Column 3(’Col3’) is too large for the specified buffer size.
- 在SQL2005/SQL2008中CTE用法差异
- ok6410 4.3寸lcd 时间参数的设定
- samba配置好了后,自己无法访问,其他局域网的电脑能访问原因。
- 表单对象属性过滤选择器
- JAVA List 排序 冒泡排序
- ORA-12899 – Value too large for column string
- 求子数组的最大和
- documentation refers to LinuxCNC 2.6.0
- Mysql_字符乱码问题
- 项目管理实践【三】每日构建【Daily Build Using CruiseControl.NET and MSBuild】
- INF文件详解
- jQuery实现导航条功能
- 快速去除word中的软回车(向下箭头)
- VC6.0 显示代码行号