Forum Stats

  • 3,817,367 Users
  • 2,259,322 Discussions
  • 7,893,760 Comments

Discussions

During impdp execution I am getting repeatable error conversion error loading table

Savoir
Savoir Member Posts: 65 Blue Ribbon

During the import of one dump into target database getting the following error

01-SEP-21 15:11:50.022: KUP-11007: conversion error loading table "SOMEUSER"."SRV_EVENTS_IN_QTAB_JMS"

01-SEP-21 15:11:50.022: ORA-26093: input data column size (3) exceeds the maximum input size (1)

01-SEP-21 15:11:50.022: KUP-11009: data for row: USER_DATA : 0X'050200'

The column USER_DATA is of TYPE aq$_jms_text_message and this type exists on both source and target databases.

Username: Data Type



Regards,

Savo

Tagged:

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    using sqlplus, show the results of the following from both databases:

    desc "SOMEUSER"."SRV_EVENTS_IN_QTAB_JMS"
    select * from nls_database_parameters  where parameter='NLS_CHARACTERSET';
    

    Obviously, sustituting the real owner name for your obviously obfuscated one.

    And please copy and paste the formatted text - no screen shots.

  • Savoir
    Savoir Member Posts: 65 Blue Ribbon
    edited Sep 3, 2021 6:50PM

    Hi Ed,

    Thanks for the response. Here is the desc from the source database:

    SQL> desc "PPL_SYS_V10"."SRV_EVENTS_IN_QTAB_JMS";
    Name              Null?    Type                     
    ----------------- -------- ------------------------ 
    Q_NAME                     VARCHAR2(128)            
    MSGID             NOT NULL RAW(16 BYTE)             
    CORRID                     VARCHAR2(128)            
    PRIORITY                   NUMBER                   
    STATE                      NUMBER                   
    DELAY                      TIMESTAMP(6)             
    EXPIRATION                 NUMBER                   
    TIME_MANAGER_INFO          TIMESTAMP(6)             
    LOCAL_ORDER_NO             NUMBER                   
    CHAIN_NO                   NUMBER                   
    CSCN                       NUMBER                   
    DSCN                       NUMBER                   
    ENQ_TIME                   TIMESTAMP(6)             
    ENQ_UID                    VARCHAR2(128)            
    ENQ_TID                    VARCHAR2(30)             
    DEQ_TIME                   TIMESTAMP(6)             
    DEQ_UID                    VARCHAR2(128)            
    DEQ_TID                    VARCHAR2(30)             
    RETRY_COUNT                NUMBER                   
    EXCEPTION_QSCHEMA          VARCHAR2(128)            
    EXCEPTION_QUEUE            VARCHAR2(128)            
    STEP_NO                    NUMBER                   
    RECIPIENT_KEY              NUMBER                   
    DEQUEUE_MSGID              RAW(16 BYTE)             
    SENDER_NAME                VARCHAR2(128)            
    SENDER_ADDRESS             VARCHAR2(1024)           
    SENDER_PROTOCOL            NUMBER                   
    USER_DATA                  SYS.AQ$_JMS_TEXT_MESSAGE 
    USER_PROP                  SYS.ANYDATA             
    
    

    For second query values for both databases is AL32UTF8

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    They tables aren't the same. The very first column (Q_NAME) is of different length. I quit comparing after that first one, and will leave it as an exercise for the student to make a detailed comparison and draw his own conclusions as to why the destination database is saying some data is too long for the column.

  • Savoir
    Savoir Member Posts: 65 Blue Ribbon
    edited Sep 3, 2021 6:54PM

    Hi Eddie,

    thanks for response.

    Actually sorry this second description is from some third database which I used as example as I did not understood you correctly. Please disregard description for target database.

    This is table is created during impdp. I managed to extract DDL via this command

    impdp [email protected] directory=DATA_PUMP_DIR dumpfile=full01%U.dmp sqlfile=lsd_ddl.sql   
    

    And here when I check DDL for this table it looks like this:

    CREATE TABLE "PPL_SYS_V10"."SRV_EVENTS_IN_QTAB_JMS"
       (    "Q_NAME" VARCHAR2(128 BYTE),
            "MSGID" RAW(16),
            "CORRID" VARCHAR2(128 BYTE),
            "PRIORITY" NUMBER,
            "STATE" NUMBER,
            "DELAY" TIMESTAMP (6),
            "EXPIRATION" NUMBER,
            "TIME_MANAGER_INFO" TIMESTAMP (6),
            "LOCAL_ORDER_NO" NUMBER,
            "CHAIN_NO" NUMBER,
            "CSCN" NUMBER,
            "DSCN" NUMBER,
            "ENQ_TIME" TIMESTAMP (6),
            "ENQ_UID" VARCHAR2(128 BYTE),
            "ENQ_TID" VARCHAR2(30 BYTE),
            "DEQ_TIME" TIMESTAMP (6),
            "DEQ_UID" VARCHAR2(128 BYTE),
            "DEQ_TID" VARCHAR2(30 BYTE),
            "RETRY_COUNT" NUMBER,
            "EXCEPTION_QSCHEMA" VARCHAR2(128 BYTE),
            "EXCEPTION_QUEUE" VARCHAR2(128 BYTE),
            "STEP_NO" NUMBER,
            "RECIPIENT_KEY" NUMBER,
            "DEQUEUE_MSGID" RAW(16),
            "SENDER_NAME" VARCHAR2(128 BYTE),
            "SENDER_ADDRESS" VARCHAR2(1024 BYTE),
            "SENDER_PROTOCOL" NUMBER,
            "USER_DATA" "SYS"."AQ$_JMS_TEXT_MESSAGE" ,
            "USER_PROP" "SYS"."ANYDATA"
       ) USAGE QUEUE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"
     VARRAY "USER_DATA"."HEADER"."PROPERTIES" STORE AS SECUREFILE LOB
      ( TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
      CACHE  NOCOMPRESS  KEEP_DUPLICATES
      STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     LOB ("USER_DATA"."TEXT_LOB") STORE AS SECUREFILE (
      TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
      NOCACHE LOGGING  NOCOMPRESS  KEEP_DUPLICATES
      STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     OPAQUE TYPE ("USER_PROP") STORE AS SECUREFILE LOB (
      ENABLE STORAGE IN ROW CHUNK 8192
      CACHE  NOCOMPRESS  KEEP_DUPLICATES
      STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;