1 2 Previous Next 16 Replies Latest reply: Aug 10, 2012 2:36 PM by user10674190 RSS

    Import error: ORA-01438

    user10674190
      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
          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
            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
              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
                --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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    it is not a part table, I am opening SR on MOS.

                                    Thanks
                                    1 2 Previous Next