Ora-02374 Conversion Error Loading Table
Contents |
facing the below mentioned error: ORA-02374: conversion error loading table "MINE_USR"."POPUPS_PROJECT_BIN" ORA-12899: value too large
Ora 02372 Data For Row Import
for column MINE_TEXT (actual: 66, maximum: 64) ORA-02372: data for ora-12899 value too large for column impdp row: MINE_TEXT : 0X'45737061C3B16F6C2020202020202020202020202020202020' Details: 1.Used expdp and created a dump for the table: MINE_USR. POPUPS_PROJECT_BIN
Oracle Ora-02374 Ora-12899 Ora-02372
2.While doing a impdp on the target database : ORA-02374: conversion error loading table "MINE_USR"."POPUPS_PROJECT_BIN" ORA-12899: value too large for column MINE_TEXT (actual: 66, maximum: 64) impdp nls_length_semantics ORA-02372: data for row: MINE_TEXT : 0X'45737061C3B16F6C2020202020202020202020202020202020' Solution: When you describe the table, you will see that the column MINE_TEXT is char(64): SQL> desc MINE_USR.POPUPS_PROJECT_BIN NameNull?Type ----------------------------------------- -------- ---------------------------- MINE_IDXNOT NULL NUMBER(5) MINE_IDXNOT NULL NUMBER(5) MINE_COLUMNNUMBER(5) MINE_VALUENUMBER(5) MINE_TEXTCHAR(64) Execute the below to modify the column of the table: SQL> alter table MINE_USR.POPUPS_PROJECT_BIN modify ora 12899 value too large for column during import 11g MINE_TEXT CHAR(66); Table altered. Now perform the import using the below command: impdp directory=DATA_PUMP_DIR dumpfile=MINE_USR.dmp logfile=MINE_USR.log tables=MINE_USR.POPUPS_PROJECT_BIN table_exists_action=truncate Posted by Sandesh Achar at 12:56 Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: Resolving ORA-02374: conversion error loading table during impdp 2 comments: Mark7 March 2015 at 10:52Very helpful, thanks.ReplyDeleteYounus Islam14 October 2015 at 22:30Really helpfull Thanks.ReplyDeleteAdd commentLoad more... Newer Post Older Post Home Subscribe to: Post Comments (Atom) Search This Blog About Me : Sandesh Achar Round Rock, Texas, United States View my complete profile What is Oracle Apps ? Oracle E-Business Suite Oracle Database All about database checkpoints and Partial checkpoint in 11.2.0.2 Automatic Diagnostic Repository Command Interpreter: ADRCI in Oracle 11g How to recover a cloned database. Oracle VM Semaphores and Shared memory Syntax we use in database for administration. Upgrade Oracle Database Oracle Home from 32 bit to 64 bi Views we use in the database. Why do have 2 sets of sql
for column. The root cause is the default semantics in a database being BYTE SQL>
Oracle Impdp Ora 02374 Ora 12899
select VALUE, ISDEFAULT from v$parameter where NAME='nls_length_semantics' VALUE ISDEFAULT ------- --------- BYTETRUE It means, one char equals one byte. But after conversion, one char is larger than one byte and does not fit any longer. single-byte SQL> select VALUE from nls_database_parameters where parameter='NLS_CHARACTERSET'; VALUE ------------- WE8MSWIN1252 SQL> create http://knoworacleappsdba.blogspot.com/2013/11/resolving-ora-02374-conversion-error.html table t(x char(1)); Table created. SQL> insert into t values ('é'); 1 row created. SQL> commit; Commit complete. $ expdp scott/tiger dumpfile=t.dmp tables=t . . exported "SCOTT"."T"1 rows multi-byte SQL> select VALUE from nls_database_parameters where parameter='NLS_CHARACTERSET'; VALUE ----------- UTF8 $ impdp scott/tiger dumpfile=t.dmp Processing object type TABLE_EXPORT/TABLE/TABLE Processing object http://laurentschneider.com/wordpress/2014/09/import-into-utf8-database.html type TABLE_EXPORT/TABLE/TABLE_DATA ORA-02374: conversion error loading table "SCOTT"."T" ORA-12899: value too large for column X (actual: 2, maximum: 1) ORA-02372: data for row: X : 0X'E9' . . imported "SCOTT"."T"0 out of 1 rows How do I import my data? 1) import the metadata $ impdp scott/tiger dumpfile=t.dmp content=metadata_only Processing object type TABLE_EXPORT/TABLE/TABLE 2) change the char_used of the column(s) from (B)yte to (C)har SQL> select column_name, char_used, data_length, data_type from user_tab_columns where table_name='T' and char_used='B'; COLUMN_NAMEC DATA_LENGTH DATA_TYPE ------------ - ----------- --------- XB 1 CHAR SQL> alter table t modify x char(1 char); Table altered. 3) import the data $ impdp scott/tiger dumpfile=t.dmp content=data_only Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."T"1 rows 4) check SQL> select x, length(x), lengthb(x) from t; XLENGTH(X) LENGTHB(X) - ---------- ---------- é12 My column has now a length of one char and two bytes. Share this:ShareLinkedInGoogleTwit
too large forcolumn Posted on March 27, 2014 by kmpdba During the import i faced the following error - ORA-02374: conversion error loading table "CONSOLIDATION"."CLIENT_OPPORTUNITY" ORA-12899: value too large for https://kmpdba.wordpress.com/2014/03/27/ora-02374-conversion-error-loading-table-ora-12899-value-too-large-for-column/ column DESCRIPTION (actual: 259, maximum: 255) ORA-02372: data for row: DESCRIPTION : ‘$1,000,000 Equipment Term Loan capped at $250,000 After analysing i found that in Production Database "NLS_CHARACTERSET" value was "WE8MSWIN1252" and in the UAT Database "NLS_CHARACTERSET" value was "AL32UTF8" To find out the NLS_CHARACTERSET value use the below query sql> select * from NLS_DATABASE_PARAMETERS; So i changed the "NLS_CHARACTERSET" value to value too "WE8MSWIN1252" in the UAT database as bellow - sql> shutdown sql> startup restrict; sql> ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252; sql> shutdown; sql> startup; It worked for me🙂 Rate this:Share this:TwitterFacebookLike this:Like Loading... Related This entry was posted in Oracle Datapump. Bookmark the permalink. ← ORA-20800: JCS-02409: User KMPDBA cannot be dropped because it is still inuse ORA-01652: unable to extend tempsegment → value too large Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required) Website You are commenting using your WordPress.com account. (LogOut/Change) You are commenting using your Twitter account. (LogOut/Change) You are commenting using your Facebook account. (LogOut/Change) You are commenting using your Google+ account. (LogOut/Change) Cancel Connecting to %s Notify me of new comments via email. Notify me of new posts via email. Search for: Recent Posts Patching Oct 2015 PSU to 11.2.0.4 using opatch auto ( Standalone with ASM) Scanning FC-LUN’s in RedhatLinux ORA-01652: unable to extend tempsegment ORA-02374: conversion error loading table , ORA-12899: value too large forcolumn ORA-20800: JCS-02409: User KMPDBA cannot be dropped because it is still inuse Archives December 2015 May 2015 April 2014 March 2014 September 2013 July 2013 May 2012 April 2012 January 2012 December 2011 Categories Oracle Database Oracle Datapump Oracle Enterprise Manager Grid Control Oracle Golden Gate PSU patching Uncategorized Meta Register Log in Entries RSS Comments RSS WordPress.com kmpdba Create a free website or blog at WordPress.com. %d bloggers like this: