1 2 Previous Next 18 Replies Latest reply on Apr 7, 2014 5:57 AM by Fran

    ORA-12899: value too large for column "SYS"

    jamiguel77

      hi all i installed oracle xe on a Windows Xp machine, asked me for a password and i typed: 123

       

      then i have a backup filenamed myback.dmp and imported:

       

      imp 'sys/123 AS SYSDBA'  file=myback.dmp full=yes log=implog.log

       

      sample messages: when importing

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

      imp 'sys/linuxx AS SYSDBA' file=fierros.dmp full=yes log=implog.txt

       

       

      Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production

       

       

      Export file created by EXPORT:V09.02.00 via conventional path

       

       

      Warning: the objects were exported by FIERROS, not by you

       

       

      import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

      import server uses AL32UTF8 character set (possible charset conversion)

      . importing FIERROS's objects into SYS

      . importing FIERROS's objects into SYS

      . . importing table                      "FIERROS"

      IMP-00019: row rejected due to ORACLE error 12899

      IMP-00003: ORACLE error 12899 encountered

      ORA-12899: value too large for column "SYS"."FIERROS"."CARACTER" (actual: 2, maximum: 1)

       

      hi all how to fix this error? thanks

        • 1. Re: ORA-12899: value too large for column "SYS"
          Fran

          No using SYS, use SYSTEM or other user instead. Oracle recommend it.

          • 2. Re: ORA-12899: value too large for column "SYS"
            KarK

            Hi,

             

            Are you want to import the objects into 'SYS' user ?

            Dont, use SYS user to export and import.

             

            Warning: the objects were exported by FIERROS   --- Try with the user 'FIERROS'

            • 3. Re: ORA-12899: value too large for column "SYS"
              onkar.nath

              you are importing the data in SYS schema which I believe is wrong and you don't want to do that. use FIERRORS as username while importing and it will work. If you are importing in some other schema then use REMAP_SCHEMA option.

               

              Onkar

              • 4. Re: ORA-12899: value too large for column "SYS"
                Suntrupth

                SYS shouldn't be used as others have already pointed out.

                 

                 

                Your Import failed due to the fact that you are importing into an AL32UTF8 database when most likely export was done with NLS_LANG set to WE8MSWIN1252.

                 

                 

                WE8MSWIN1252 is a single byte characterset and when importing into an AL32UTF8 which is a multi byte characterset , each character would be stored in 1 or more byte.

                 

                 

                So, either expand the column CARACTER to 2 char/byte (datatype) using alter table modify .. OR truncate some data so that it can fit in your column (most likely you may not choose this option).

                 

                 

                There is characterset conversion which is taking place and hence the error.

                 

                 

                Datapump export/import doesn't rely on your NLS_LANG so you could use impdp/expdp hence forth.

                 

                 

                Regards,

                Suntrupth

                • 5. Re: ORA-12899: value too large for column "SYS"
                  RNi

                  Hi Onkar,

                   

                  the op uses exp/imp with no option REMAP_SCHEMA

                  exp/imp uses FROM_USER <user> TO_USER <user>

                  without this option objects never imported in another schema. The log is interpreted as:

                  find and import objects of the user starting the import

                  find and import objects of all other users within the exportdump because of option full=yes

                  objects owned by SYS are never ever exp/imported (as far as i know)

                   

                  regards

                  RNi

                  • 6. Re: ORA-12899: value too large for column "SYS"
                    jamiguel77

                    Hi SunTrupth how are you.

                     

                    Thanks for answer.

                     

                    i created a user:

                     

                    create user "UFIERROS" profile "DEFAULT" IDENTIFIED BY "linuxx" DEFAULT TAB

                    LESPACE "DESA_DAT" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "DESA_DAT" ACC

                    OUNT UNLOCK;

                     

                    and granted:

                    SQL> GRANT "CONNECT" TO "UFIERROS";

                     

                    then try import:

                     

                    C:\oraclexe\app\oracle\product\11.2.0\server\bin>imp system/linuxx@XE fromuser=FIERROS touser=UFIERROS file=fierros.dmp" log="mylog.log"

                     

                    but continue the error:

                     

                    . importing FIERROS's objects into UFIERROS

                    . . importing table                      "FIERROS"

                    IMP-00019: row rejected due to ORACLE error 12899

                    IMP-00003: ORACLE error 12899 encountered

                    ORA-12899: value too large for column "UFIERROS"."FIERROS"."CARACTER" (actual: 2, maximum: 1)

                     

                    how to alter the table?

                     

                    thanks

                    • 7. Re: ORA-12899: value too large for column "SYS"
                      Srini Chavali-Oracle

                      ORA-12899: value too large for column "UFIERROS"."FIERROS"."CARACTER" (actual: 2, maximum: 1)

                      The error is obvious - the dump file contains a field of 2 characters while the target field is defined as 1.

                       

                      Pl post the table definitions of the source and target tables, along with the complete export command used to create the dump file and the first 30 lines of the export log file

                      • 8. Re: ORA-12899: value too large for column "SYS"
                        jamiguel77

                        Hi Srini, i dont have a Target tables...

                         

                        i have a file myback.dmp

                        installed oraclexe

                        then imported the file:

                         

                        C:\oraclexe\app\oracle\product\11.2.0\server\bin>imp system/linuxx@XE fromuser=FIERROS touser=UFIERROS file=fierros.dmp" log="mylog.log"

                         

                        understand me?

                         

                        thanks.

                        • 9. Re: ORA-12899: value too large for column "SYS"
                          Srini Chavali-Oracle

                          Pl post a description of the source table, the characterset of the source database, the complete export command used and the first 30 lines of the export log.

                          • 10. Re: ORA-12899: value too large for column "SYS"
                            jamiguel77

                            Srini, i dont know how to exported...

                            i only have the file myback.dmp

                             

                            thanks friend

                            • 11. Re: ORA-12899: value too large for column "SYS"
                              Srini Chavali-Oracle

                              That information is required to figure out the issue, otherwise this will be almost impossible to solve.

                              • 12. Re: ORA-12899: value too large for column "SYS"
                                jamiguel77

                                Srini, understand. but havent the information that you required.

                                 

                                not exist other way?

                                sample:

                                 

                                change the column varchar(1) to varchar(2)

                                 

                                and then delete the data of the table, and then import only the table?

                                 

                                Thanks

                                • 13. Re: ORA-12899: value too large for column "SYS"
                                  Srini Chavali-Oracle

                                  You could try that, but the errors indicate a more serious issue, for which your bandaid solution may not be sufficient

                                  • 14. Re: ORA-12899: value too large for column "SYS"
                                    Solomon Yakobson

                                    Srini Chavali-Oracle wrote:

                                     

                                    You could try that, but the errors indicate a more serious issue, for which your bandaid solution may not be sufficient

                                    And why do you think it is a bandade solution. Based on:

                                     

                                    import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

                                    import server uses AL32UTF8 character set (possible charset conversion)

                                     

                                    We know target database used multi-byte character set while import dump is using single character set. We don't know source database character set but based on the error it is single byte character set. Also, based on ORA-12899: value too large for column "SYS"."FIERROS"."CARACTER" (actual: 2, maximum: 1) we know source databse column CARACTER in table FIERROS was declared as 1 byte string. As a result, it was also created in target database as 1 byte string. However at least one column value occupies 2 bytes when converted to AL32UTF8 and obviously doen't fit. This is typical situation when importing to a database with character set where some source characters occupy more space. And solution is to make a note of all such columns, run metadata only import, alter length of noted columns and then do data import. I think there is MOS atricle explaining all this (don't have doc id handy).

                                     

                                    SY.

                                    1 2 Previous Next