DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Export (impdp) in one character set and import (impdp) in different

  1. #1
    Join Date
    Jul 2003
    Posts
    136

    Exclamation Export (impdp) in one character set and import (impdp) in different

    Environment: Oracle 10g, WIndows 2003

    When I import data from

    Source: 10.1.0.2.0 WE8MSWIN1252 AMERICAN_AMERICA database
    with nls_length_semantics BYTE
    Target: 10.2.0.1.0 AL32UTF8 AMERICAN_AMERICA database
    with nls_length_semantics BYTE

    I get error for some rows and those rows do not get imported, rest everything goes fine.

    ORA-02374: conversion error loading table "ABC"
    ORA-12899: value too large for column MYFIELD (actual: 263, maximum: 255)
    ORA-02372: data for row: MYFIELD : 'per e-mail received 09oct06 ..

    Please note that problem comes only with those rows where whole length is cooupied.. e.g. IF I have char(10), and all 10 bytes are occupied, then I get this problem. But If I have at least 1 byte free, then import is successful.

    I am not sure changing source table column length will cause an issue or not due to application constraints.

    I have tried setting Target db's nls_length_semantics to CHAR but that didn't help - I still get the error. Changing Target Column's length cause other issue with triggers and doesn't import any row - So I cann't use that option. Also due to many different db migration that we need to do, I donnot think field level changes will be good option.

    I am not sure, how safe is to use nls_length_semantics to CHAR in Source db and then try.

    By the way, I have read this:
    http://forums.oracle.com/forums/thre...hreadID=267176

    Is there any workaround/Option/advice I can use.

    -D

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    You are moving from WE8MSWIN1252 to AL32UTF8 (Unicode)

    Your affected columns on target database are too small and can't handle the growth-in-bytes or some Unicode characters.

    In your example above MYFIELD column is as big as 263 bytes while column size is just 255

    You might want to check Metalink Note:119119.1

    A workaround -if permitted by application, is to pre-create affected tables in Target database giving offending columns a larger size. In this case don't forget to use skip=y during Import.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2003
    Posts
    136
    Quote Originally Posted by PAVB

    You might want to check Metalink Note:119119.1

    A workaround -if permitted by application, is to pre-create affected tables in Target database giving offending columns a larger size. In this case don't forget to use skip=y during Import.
    Thanks PAVB.
    That will help.
    -D

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width