This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 10, 2012 12:36 PM by user10674190 RSS

Import error: ORA-01438

user10674190 Newbie
Currently Being Moderated
Hi all,

I am importing full database from 9i to 11g.

I did the full export from Oracle 9i, and I didn't get any error:

.
.
.
. . exporting table OCORRENCIA 101 rows exported
. . exporting table ORIGDEST 176555 rows exported
. . exporting table ORIGDESTINBOUND 237 rows exported
. . exporting table OT 3075243 rows exported
. . exporting table PARAM 1 rows exported
. . exporting table PARAMMGV 1 rows exported
. . exporting table PARM 0 rows exported
.
.
.
Export terminated successfully without warnings.

When try to import to Oracle11g, I got ORA-01438 on OT table:

. . importing table "OT"
IMP-00058: ORACLE error 1438 encountered
ORA-01438: valor maior que a precisão especificada usado para esta coluna
IMP-00028: partial import of previous table rolled back: 797323 rows rolled back
. . importing table "PARAM" 1 rows imported
. . importing table "PARAMMGV" 1 rows imported


Can anybody help me?

Thanks in advance.


Leonardo.
  • 1. Re: Import error: ORA-01438
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Pl post the database charactersets of the two databases, the complete exp and imp commands used, along with the first 10 lines of the exp and imp log files.

    HTH
    Srini
  • 2. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    Hi Srini.

    Thanks for your replay.

    Please, see below:


    DATABASE CHARACTERSETS OF THE TWO DATABASES:

    --Oracle 9i:

    SQL> select *
    2 from sys.PROPS$
    3 where name like '%CHARAC%'
    4 /

    NAME VALUE$ COMMENT$
    ------------------------------ ---------------------------------------- ---------------------------
    NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set
    NLS_NUMERIC_CHARACTERS ., Numeric characters
    NLS_CHARACTERSET WE8ISO8859P1 Character set

    --Oracle 11g:

    SQL> select *
    2 from sys.PROPS$
    3 where name like '%CHARAC%'
    4 /

    NAME VALUE$ COMMENT$
    ------------------------------ ---------------------------------------- ----------------------------
    NLS_NUMERIC_CHARACTERS ., Numeric characters
    NLS_CHARACTERSET WE8ISO8859P1 Character set
    NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set


    ======================================================================================================

    THE COMPLETE EXP AND IMP COMMANDS USED:

    --Oracle 9i:

    export ORACLE_SID=sgtdox

    exp userid=system/**** full=Y file=sgtdox_full log=sgtdox_full


    --Oracle 11g:

    export ORACLE_SID=sgtdox_2

    imp userid=system/**** full=y file=sgtdox_full.dmp log=sgtdox_full_imp.log

    =====================================================================================================


    ALONG WITH THE FIRST 10 LINES OF THE EXP AND IMP LOG FILES.

    --Oracle 9i:

    Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    JServer Release 9.2.0.8.0 - Production
    Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

    About to export the entire database ...
    . exporting tablespace definitions
    . exporting profiles
    . exporting user definitions
    . exporting roles
    . exporting resource costs
    . exporting rollback segment definitions
    . exporting database links
    . exporting sequence numbers
    . exporting directory aliases
    . exporting context namespaces
    . exporting foreign function library names
    . exporting PUBLIC type synonyms
    . exporting private type synonyms
    . exporting object type definitions
    . exporting system procedural objects and actions
    . exporting pre-schema procedural objects and actions


    --Oracle 11g:

    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
    Production
    With the Partitioning, Real Application Clusters and Automatic Storage Managemen
    t options

    Export file created by EXPORT:V09.02.00 via conventional path
    import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
    . importing SYSTEM's objects into SYSTEM
    IMP-00017: following statement failed with ORACLE error 1119:
    "CREATE UNDO TABLESPACE "RBS" BLOCKSIZE 8192 DATAFILE '/storage/u03/oradata"
    "/sgtdox/rbs/undo01.dbf' SIZE 524288000 EXTENT MANAGEMENT LOCAL "
    IMP-00003: ORACLE error 1119 encountered
    ORA-01119: erro ao criar o arquivo '/storage/u03/oradata/sgtdox/rbs/undo01.dbf'
    de banco de dados
  • 3. Re: Import error: ORA-01438
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Thanks - these commands look good. Can you pl post a description of the OT table from the two databases ? Do the columns match exactly ?

    Import Fails with IMP-00058, ORA-1438 Errors [ID 185229.1]

    HTH
    Srini
  • 4. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    --Oracle 9i:*

    SQL> desc sgt.ot
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    IDOT NOT NULL NUMBER(10)
    NUMERO NOT NULL VARCHAR2(8)
    IDTARIFA NUMBER(6)
    IDFAIXA NUMBER(6)
    IDVERCONTRATO NUMBER(4)
    SUBTIPO NOT NULL NUMBER(2)
    IDEMBARCADOR NUMBER(10)
    STATUS NOT NULL NUMBER(2)
    IDROTA NUMBER(4)
    IDTRANSPORTADOR NUMBER(10)
    IDCLASSEVEIC NUMBER(4)
    IDTIPOCARGA NOT NULL NUMBER(3)
    IDMODALTRANSP NOT NULL NUMBER(2)
    IDFATURA NUMBER(10)
    IDTIPOTRANSP NOT NULL NUMBER(3)
    ROMANEIO NUMBER(12)
    DATAPREVISTA DATE
    DATAEMBARQUE DATE
    FRETEPESO NOT NULL NUMBER(15,2)
    FRETEVALOR NOT NULL NUMBER(15,2)
    FRETEGENER NOT NULL NUMBER(15,2)
    FRETERECUR NOT NULL NUMBER(15,2)
    FRETEIMPOS NOT NULL NUMBER(15,2)
    VALORAJUSTE NUMBER(15,2)
    PLACAVEIC VARCHAR2(10)
    NOMEMOT VARCHAR2(30)
    DOCTOMOT VARCHAR2(30)
    USUARIO NOT NULL VARCHAR2(30)
    DATAHORA NOT NULL DATE
    ARQREM VARCHAR2(40)
    FRETEMORTO VARCHAR2(1)
    TIPOOT VARCHAR2(1)
    IDOTMAE NUMBER(10)
    DHINICIO DATE
    DHFIMPREV DATE
    TEMPOPREV NUMBER(4)
    DHFIM DATE
    TEMPOTRANSITO NUMBER
    AUTOIMP VARCHAR2(1)
    OBSERVACAO VARCHAR2(64)
    IDCONTROLEKOMBI NUMBER(15)
    CARGADIRETA VARCHAR2(1)
    KMCALCULO NUMBER(10)



    --Oracle 11g:*

    SQL> desc sgt.ot
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    IDOT NOT NULL NUMBER(10)
    NUMERO NOT NULL VARCHAR2(8)
    IDTARIFA NUMBER(6)
    IDFAIXA NUMBER(6)
    IDVERCONTRATO NUMBER(4)
    SUBTIPO NOT NULL NUMBER(2)
    IDEMBARCADOR NUMBER(10)
    STATUS NOT NULL NUMBER(2)
    IDROTA NUMBER(4)
    IDTRANSPORTADOR NUMBER(10)
    IDCLASSEVEIC NUMBER(4)
    IDTIPOCARGA NOT NULL NUMBER(3)
    IDMODALTRANSP NOT NULL NUMBER(2)
    IDFATURA NUMBER(10)
    IDTIPOTRANSP NOT NULL NUMBER(3)
    ROMANEIO NUMBER(12)
    DATAPREVISTA DATE
    DATAEMBARQUE DATE
    FRETEPESO NOT NULL NUMBER(15,2)
    FRETEVALOR NOT NULL NUMBER(15,2)
    FRETEGENER NOT NULL NUMBER(15,2)
    FRETERECUR NOT NULL NUMBER(15,2)
    FRETEIMPOS NOT NULL NUMBER(15,2)
    VALORAJUSTE NUMBER(15,2)
    PLACAVEIC VARCHAR2(10)
    NOMEMOT VARCHAR2(30)
    DOCTOMOT VARCHAR2(30)
    USUARIO NOT NULL VARCHAR2(30)
    DATAHORA NOT NULL DATE
    ARQREM VARCHAR2(40)
    FRETEMORTO VARCHAR2(1)
    TIPOOT VARCHAR2(1)
    IDOTMAE NUMBER(10)
    DHINICIO DATE
    DHFIMPREV DATE
    TEMPOPREV NUMBER(4)
    DHFIM DATE
    TEMPOTRANSITO NUMBER
    AUTOIMP VARCHAR2(1)
    OBSERVACAO VARCHAR2(64)
    IDCONTROLEKOMBI NUMBER(15)
    CARGADIRETA VARCHAR2(1)
    KMCALCULO NUMBER(10)
  • 5. Re: Import error: ORA-01438
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    The table descriptions also match :-) This looks to be a data corruption issue in the source database. Pl see details in the MOS Doc link I posted above.

    How many rows are in the OT table in the source ? Is there a way to determine how data got inserted into this table and to examine the data ?

    HTH
    Srini
  • 6. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    Hi Srini, thanks again for your reply.

    I've opened a SR on MOS, but, I checked this MOS Doc and I didn't see any block corruption using DBV utility.

    How many rows are in the OT table in the source ?

    --Oracle 11g:*

    SQL> select count(*) from sgt.ot
    2 /

    COUNT(*)
    ----------
    0

    --Oracle 9i:*

    SQL> select count(*) from sgt.ot
    2 /

    COUNT(*)
    ----------
    3076128
  • 7. Re: Import error: ORA-01438
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    You will not find the corruption using DBV utility - this is logical data corruption as explained in the MOS Doc. You will have to use the characterset scanner on this table in 9i

    http://docs.oracle.com/cd/B10501_01/server.920/a96529/ch11.htm - see the "Example: Single Table Scan" section - run this scan for the OT table using "tochar=WE8ISO8859P1"

    HTH
    Srini
  • 8. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    Hi Srini, for your reply.

    I don't know how can I interpret csscan log, but I put here:

    cat OT_03082012.txt


    Database Scan Summary Report

    Time Started  : 2012-08-07 17:31:06
    Time Completed: 2012-08-07 17:31:21

    Process ID         Time Started       Time Completed
    ---------- -------------------- --------------------
    +1 2012-08-07 17:31:06 2012-08-07 17:31:20+
    ---------- -------------------- --------------------

    +[Database Size]+

    Tablespace                            Used(MB)        Free(MB)       Total(MB)
    ------------------------------ --------------- --------------- ---------------
    SYSTEM                                 204,000          96,000         300,000
    RBS                                      6,000         494,000         500,000
    TEMP_TS                                   ,000            ,000            ,000
    DATA_STATSPACK                         233,000         167,000         400,000
    DATA_SGT02                           6.151,000         849,000       7.000,000
    INDX_SGT02                           5.447,000       1.553,000       7.000,000
    DBA                                       ,000         250,000         250,000
    DATA_ST_EXEL02                       1.136,000         864,000       2.000,000
    INDX_ST_EXEL02                         522,000       1.478,000       2.000,000
    DATA_SGT_SCJ02                       1.147,000         853,000       2.000,000
    INDX_SGT_SCJ02                       1.009,000         991,000       2.000,000
    DATA_SGT_NOKIA02                       288,000       1.712,000       2.000,000
    INDX_SGT_NOKIA02                       267,000         233,000         500,000
    DATA_DBA                                45,000         455,000         500,000
    USERS                                     ,000         500,000         500,000
    DATA_SGT03                             431,000       6.569,000       7.000,000
    ------------------------------ --------------- --------------- ---------------
    Total                               16.885,000      17.065,000      33.950,000

    +[Database Scan Parameters]+

    Parameter                      Value
    ------------------------------ ------------------------------------------------
    Scan type                      Selective tables
    Scan CHAR data?                YES
    Current database character set WE8ISO8859P1
    New database character set     WE8ISO8859P1
    Scan NCHAR data?               NO
    Array fetch buffer size        1024000
    Number of processes            1
    Capture convertible data?      NO
    ------------------------------ ------------------------------------------------

    +[Scan Summary]+

    Some character type application data are not convertible to the new character set

    +[Data Dictionary Conversion Summary]+

    Datatype                    Changeless      Convertible      Exceptional            Total
    --------------------- ---------------- ---------------- ---------------- ----------------
    VARCHAR2
    CHAR
    LONG
    CLOB
    --------------------- ---------------- ---------------- ---------------- ----------------
    Total

    +[Application Data Conversion Summary]+

    Datatype                    Changeless      Convertible      Exceptional            Total
    --------------------- ---------------- ---------------- ---------------- ----------------
    VARCHAR2                    34.023.043                0                1       34.023.044
    CHAR
    LONG
    CLOB
    --------------------- ---------------- ---------------- ---------------- ----------------
    Total                       34.023.043                0                1       34.023.044

    +[Distribution of Convertible Data per Table]+

    USER.TABLE                                                   Convertible      Exceptional
    ------------------------------------------------------- ---------------- ----------------
    SGT.OT_03082012                                                        0                1
    ------------------------------------------------------- ---------------- ----------------

    +[Distribution of Convertible Data per Column]+

    USER.TABLE|COLUMN                                            Convertible      Exceptional
    ------------------------------------------------------- ---------------- ----------------
    SGT.OT_03082012|AUTOIMP                                                0                1
    ------------------------------------------------------- ---------------- ----------------

    +[Indexes to be Rebuilt]+

    USER.INDEX on USER.TABLE(COLUMN)
    -----------------------------------------------------------------------------------------
    -----------------------------------------------------------------------------------------





    ============================================================

    cat OT_03082012.out


    Character Set Scanner v1.1 : Release 9.2.0.1.0 - Production on Ter Ago 7 17:30:54 2012

    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


    Username:
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
    With the Partitioning and Oracle Label Security options
    JServer Release 9.2.0.8.0 - Production

    Enumerating table to scan...

    +. process 1 scanning SGT.OT_03082012[AAAGlFAAXAAAAAJAAA]+
    +. process 1 scanning SGT.OT_03082012[AAAGlFAASAAAAAJAAA]+
    +. process 1 scanning SGT.OT_03082012[AAAGlFAAYAAAAAJAAA]+
    +. process 1 scanning SGT.OT_03082012[AAAGlFAAWAAAAAJAAA]+

    Creating Database Scan Summary Report...

    Creating Individual Exception Report...

    Scanner terminated successfully.


    ============================================================

    cat OT_03082012.err

    Database Scan Individual Exception Report


    +[Database Scan Parameters]+

    Parameter                      Value
    ------------------------------ ------------------------------------------------
    Scan type                      Selective tables
    Scan CHAR data?                YES
    Current database character set WE8ISO8859P1
    New database character set     WE8ISO8859P1
    Scan NCHAR data?               NO
    Array fetch buffer size        1024000
    Number of processes            1
    Capture convertible data?      NO
    ------------------------------ ------------------------------------------------

    +[Data Dictionary individual exceptions]+


    +[Application data individual exceptions]+

    User  : SGT
    Table : OT_03082012
    Column: AUTOIMP
    Type  : VARCHAR2(1)
    Number of Exceptions         : 1
    Max Post Conversion Data Size: 1

    ROWID              Exception Type      Size Cell Data(first 30 bytes)
    ------------------ ------------------ ----- ------------------------------
    AAAGlFAAWAAAAzrAAV lossy conversion
    ------------------ ------------------ ----- ------------------------------
  • 9. Re: Import error: ORA-01438
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    OK - this is showing that there is 1 row in table SGT.OT_03082012 (rowid AAAGlFAAWAAAAzrAAV) that has an invalid value in the AUTOIMP column. Can you post the output of this statement
    SQL> select autoimp, DUMP(autoimp) from SGT.OT_03082012 where rowid='AAAGlFAAWAAAAzrAAV';
    HTH
    Srini
  • 10. Re: Import error: ORA-01438
    Sergiusz Wolicki (Oracle) Expert
    Currently Being Moderated
    The discussed error is ORA-01438, which applies to numeric columns only. Therefore, I see not much sense in looking at CSSCAN report, which pertains to character columns. Though, as table definitions are identical, you should look for logical corruption. I wonder if SQL trace with bind variable info during import would help narrowing down the problem. To reduce the number of tracing information, you should try to just import the failing table.


    -- Sergiusz
  • 11. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    SQL> col DUMP(autoimp) for a50
    SQL> select autoimp, DUMP(autoimp) from SGT.OT_03082012 where rowid='AAAGlFAAWAAAAzrAAV';

    A DUMP(AUTOIMP)
    - --------------------------------------------------
    ¿ Typ=1 Len=1: 128
  • 12. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    I tried to delete the row, but now, I got a ORA-00600

    SQL> delete from SGT.OT_03082012
    2 where rowid='AAAGlFAAWAAAAzrAAV';

    ERROR at line 1:
    ORA-00600: internal error code, arguments: [6704], [2], [29721], [46204415],
    [], [], [], []
  • 13. Re: Import error: ORA-01438
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Is this a partitioned table ?

    Only Support can troubleshoot ORA-00600 errors - pl open an SR with Support

    HTH
    Srini
  • 14. Re: Import error: ORA-01438
    user10674190 Newbie
    Currently Being Moderated
    it is not a part table, I am opening SR on MOS.

    Thanks
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points