This discussion is archived
1 2 Previous Next 20 Replies Latest reply: May 12, 2011 8:21 PM by Hemant K Chitale RSS

What character should I set for the target database?

548150 Pro
Currently Being Moderated
What character should I set for the target database?
what NLS should I set for target database?
I imp data from the .dmp file and get the following error message:
Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16
   NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
export server uses UTF8 NCHAR character set (possible 
   ncharset conversion)
...
IMP-00003: ORACLE error 1861 encountered
ORA-01861: literal does not match format string
  • 1. Re: What character should I set for the target database?
    sb92075 Guru
    Currently Being Moderated
    01861, 00000, "literal does not match format string"
    // *Cause:  Literals in the input must be the same length as literals in
    //  the format string (with the exception of leading whitespace).  If the
    //  "FX" modifier has been toggled on, the literal must match exactly,
    //  with no extra whitespace.
    // *Action: Correct the format string to match the literal.
    Re: What character should I set for the target database?
    When you start with the wrong question, no matter how good an answer you get, it won't matter very much.

    Edited by: sb92075 on May 11, 2011 8:23 PM
  • 2. Re: What character should I set for the target database?
    548150 Pro
    Currently Being Moderated
    From the output, you can see the NSL set on the exp and imp servers don't match. Is it a problem?
  • 3. Re: What character should I set for the target database?
    sb92075 Guru
    Currently Being Moderated
    Handle:      jetq
    Status Level:      Pro (725)
    Registered:      Nov 24, 2006
    Total Posts:      1,031
    Total Questions:      152 (122 unresolved)
    so many questions & so few answers.
    :-(
    From the output, you can see the NSL set on the exp and imp servers don't match. Is it a problem?
    What is displayed may indicate a problem, BUT the error code/message has nothing to do with characterset.

    A characterset incompatibility might garble some characters, but won't throw the error you have.

    You have not posted enough detail to give me a reasonable starting point to guess root cause.
  • 4. Re: What character should I set for the target database?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    You should create the new database in the correct characterset.

    However, the ORA 1861 error relates to the Date Format. You likely haven't set the NLS_DATE_FORMAT in your import session environment to match the date format used at the exported database.


    Hemant K Chitale
  • 5. Re: What character should I set for the target database?
    orafad Oracle ACE
    Currently Being Moderated
    jetq wrote:
    What character should I set for the target database?
    what NLS should I set for target database?
    How should we know? Not sure what you mean though.
    I imp data from the .dmp file and get the following error message:
    ...
    export client uses US7ASCII character set (possible charset conversion)
    Please post heading lines from export log.

    And post nls_characterset from source database. Target likely has WE8MSWIN1252 (from imp session log), correct?

    What languages (alphabets) should the database support?

    IMP-00003: ORACLE error 1861 encountered
    ORA-01861: literal does not match format string
    The language and territory parts of NLS_LANG also affect the import session. Date format string setting, if not set otherwise (as suggested above), is "inherited" from defaults provided by territory part of NLS_LANG. That's simplified. But NLS parameters and behaviour is of course well documented, so you could/should reference the docs.

    For more specific NLS discussions there's the dedicated forum: {forum:id=50}.

    Edited by: orafad on May 12, 2011 9:29 AM
  • 6. Re: What character should I set for the target database?
    548150 Pro
    Currently Being Moderated
    Connected to: Oracle Database 10g Enterprise Edition Release 
    10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options
    
    Export file created by EXPORT:V10.02.01 via conventional path
    import done in WE8MSWIN1252 character set and AL16UTF16 
    NCHAR character set
    export client uses US7ASCII character set (possible charset 
    conversion)
    export server uses UTF8 NCHAR character set (possible 
    ncharset conversion)
    . importing SYSTEM's objects into SYSTEM
    ...
    IMP-00003: ORACLE error 1861 encountered
    ORA-01861: literal does not match format string
    . . importing table    "ACC_POSITIVE_NEGATIVE_ACL"          
    0 rows imported
    IMP-00017: following statement failed with ORACLE error 1861:
     " ALTER TABLE "ACC_USER_PROFILE" MODIFY ("UP_EXPIRY_DATE" 
    DEFAULT '30000101'"
     " )"
    Edited by: jetq on May 12, 2011 8:44 AM
  • 7. Re: What character should I set for the target database?
    548150 Pro
    Currently Being Moderated
    SQL> show parameter nls_date_format;
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------
    nls_date_format                      string
    
    SQL> select sysdate from dual;
    
    SYSDATE
    ---------
    12-MAY-11
    
    SQL> 
    Edited by: jetq on May 12, 2011 8:47 AM
  • 8. Re: What character should I set for the target database?
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    The date format in the dump file is very different :

    " ALTER TABLE "ACC_USER_PROFILE" MODIFY ("UP_EXPIRY_DATE" DEFAULT '30000101'")

    I can't figure out what it is !
    But it isn't DD-MON-RR

    Hemant K Chitale
  • 9. Re: What character should I set for the target database?
    sybrand_b Guru
    Currently Being Moderated
    Apparently it is YYYMMDD, as the value look to me like 'plus infinite'
    I once had an important fail because of a different date mask, where the default on the client, was different from the default on the server.
    Yet another reason to use to_date always!

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 10. Re: What character should I set for the target database?
    548150 Pro
    Currently Being Moderated
    Thank you all.

    Now I resintall the database with UTF8 character set. Before imp, I change the client NSL_LANG value. But also get same error message except the with different character set specification:


    C:\Install\export>set nsl_lang=america_canada.us7acii
    C:\Install\export>imp SYSTEM/metex_01 FILE=dry.dmp log=imp_log_3.log full=y
    
    Import: Release 10.2.0.1.0 - Production on Thu May 12 12:08:18 2011
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    Export file created by EXPORT:V10.02.01 via conventional path
    import done in WE8MSWIN1252 character set and UTF8 NCHAR character set
    import server uses UTF8 character set (possible charset conversion)
    export client uses US7ASCII character set (possible charset conversion)
    . importing SYSTEM's objects into SYSTEM
    . importing DRY's objects into DRY
    IMP-00003: ORACLE error 1435 encountered
    ORA-01435: user does not exist
    IMP-00015: following statement failed because the object already exists:
     "CREATE PUBLIC SYNONYM "XMLTYPE" FOR "SYS"."XMLTYPE""
    Import terminated successfully with warnings.
    Some error message from log file:
    IMP-00017: following statement failed with ORACLE error 1861:
     " ALTER TABLE "BENEFIT_LIST" MODIFY ("REC_TERMINATION_DT" DEFAULT '30000101'"
     " )"
    IMP-00003: ORACLE error 1861 encountered
    ORA-01861: literal does not match format string
    IMP-00017: following statement failed with ORACLE error 1861:
     " ALTER TABLE "BENEFIT_LIST_RULE" MODIFY ("REC_TERMINATION_DT" DEFAULT '3000"
     "0101' )"
    IMP-00003: ORACLE error 1861 encountered
    ORA-01861: literal does not match format string
    . . importing table         "BENEFIT_LIST_RULE_CD"         11 rows imported
    IMP-00017: following statement failed with ORACLE error 1861:
     " ALTER TABLE "BENEFIT_MANAGER" MODIFY ("REC_TERMINATION_DT" DEFAULT '300001"
     "01' )"
    Edited by: jetq on May 12, 2011 12:31 PM
  • 11. Re: What character should I set for the target database?
    sb92075 Guru
    Currently Being Moderated
    But also get same error message except the with different character set specification:
    same as WHAT?
  • 12. Re: What character should I set for the target database?
    548150 Pro
    Currently Being Moderated
    IMP-00003: ORACLE error 1861 encountered
    ORA-01861: literal does not match format string
  • 13. Re: What character should I set for the target database?
    sb92075 Guru
    Currently Being Moderated
    SLOW learner?

    below from previous response
    the error code/message has nothing to do with characterset.
  • 14. Re: What character should I set for the target database?
    548150 Pro
    Currently Being Moderated
    Now I think you are right.
    How to deal with it?
    Change the NLS_date_format?
    Thank you
1 2 Previous Next

Legend

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