9 Replies Latest reply: Feb 8, 2012 8:44 AM by John O'Toole RSS

ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)

fdussauchoy

Hello,

 

We have an issue with the BSARA (version 1.02, BladeLogic For Server V7.6) Reporting on step D_CONFIG_FILE_OBJECT.

The NLS_LENGTH_SEMANTICS is now set to CHAR (before it was BYTE) with the BMC support helping, but the problem is still there.

The error message is ORA-12899, value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000),

 

This object is an temporary table which is generated after each report and populated by the sql command just below:

 

insert /*+ APPEND */ into BSARA_DW.I1$_D_CONFIG_FILE_OBJECT
(
   CONFIG_FILE_OBJECT_ID,
   BLFILE_ID,
   PATH_ID,
   RULE_ID,
   RECORD_NUMBER,
   IS_LEAF,
   NAME,
   DATA_VERSION,
   COMMENTS,
   IS_DELETED,

      BL_SITE_ID,
  
   IND_UPDATE
)
select    
   C1_CONFIG_FILE_OBJECT_ID,
   C2_BLFILE_ID,
   C3_PATH_ID,
   C4_RULE_ID,
   C5_RECORD_NUMBER,
   C6_IS_LEAF,
   C7_NAME,
   C8_DATA_VERSION,
   C9_COMMENTS,
   C10_IS_DELETED,


      #BLREPORTS.var_bl_site,
  
   'I' IND_UPDATE

from   BSARA_DW.C1$_0D_CONFIG_FILE_OBJECT
where   (1=1)
minus
select
   CONFIG_FILE_OBJECT_ID,
   BLFILE_ID,
   PATH_ID,
   RULE_ID,
   RECORD_NUMBER,
   IS_LEAF,
   NAME,
   DATA_VERSION,
   COMMENTS,
   IS_DELETED,

      #BLREPORTS.var_bl_site,
  
   'I'   IND_UPDATE
from   BSARA_DW.D_CONFIG_FILE_OBJECT
where (1 = 1)

       AND BL_SITE_ID
   


      = #BLREPORTS.var_bl_site

 

However, the table BSARA_DW.C1$_0D_CONFIG_FILE_OBJECT contains 11 records whose lenght of field "NAME" is > 2000 characters, because in this table, the field "NAME" can contain up to 4000 characters.

 

So it seems there is an error in the structure of field "NAME" of the table BSARA_DW.C1 $ _0D_CONFIG_FILE_OBJECT that holds up to 4000 characters, or in the structure of the field "NAME" of the table I1$_D_CONFIG_FILE_OBJECT which can contain only 2000 characters.

 

We also tried to expand the NAME field of the permanent table D_CONFIG_FILE_OBJECT  (from 2000 to 4000 characters) hoping that the temporary table following this structure, but the table I1$_D_CONFIG_FILE_OBJECT field NAME remains at  2000 Characters.

 

Any Idea ?

 

Thank in advance.

 

Fabrice

  • 1. ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    John O'Toole

    Hi Fabrice,

     

    Can you run the following SQL Statements as the appropriate DB user:

     

    As bladelogic:

     

    describe CONFIG_FILE_OBJECT;

    Select value from system_property where name = 'DatabaseVersion';

     

    As bsara_dw:

     

    Select value from system_property where name = 'DatabaseVersion';

    describe D_CONFIG_FILE_OBJECT;

    describe I1$_D_CONFIG_FILE_OBJECT;

     

    The last one may not exist right now and thats ok if it does not.

     

    Thanks,

     

    John

  • 2. ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    fdussauchoy

    Hi John,

     

    Please find the answer to your questions below :

     

    As User BLADELOGIC :

     

    SQL> desc CONFIG_FILE_OBJECT
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    CONFIG_FILE_OBJECT_ID                     NOT NULL NUMBER(38)
    BLFILE_ID                                 NOT NULL NUMBER(38)
    PATH_ID                                            NUMBER(38)
    META_CHECKSUM_HI                                   NUMBER(22)
    META_CHECKSUM_LO                                   NUMBER(22)
    RULE_ID                                            NUMBER(38)
    RECORD_NUMBER                                      NUMBER(38)
    IS_LEAF                                            NUMBER(1)
    NAME                                               VARCHAR2(4000)
    DATA_VERSION                                       NUMBER(38)
    COMMENTS                                           VARCHAR2(2000)
    IS_DELETED                                NOT NULL NUMBER(1)


    SQL> Select value from system_property where name = 'DatabaseVersion';


    VALUE
    --------------------------------------------------------------------------------
    7.6.0

     

     


    As User BSARA_DW :

     

    SQL> desc D_CONFIG_FILE_OBJECT
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    CONFIG_FILE_OBJECT_HIST_ID                NOT NULL NUMBER(38)
    CONFIG_FILE_OBJECT_ID                     NOT NULL NUMBER(38)
    BLFILE_ID                                          NUMBER(38)
    PATH_ID                                            NUMBER(38)
    RULE_ID                                            NUMBER(38)
    RECORD_NUMBER                                      NUMBER(38)
    IS_LEAF                                            NUMBER(1)
    NAME                                               VARCHAR2(4000 CHAR)
    DATA_VERSION                                       NUMBER(38)
    COMMENTS                                           VARCHAR2(2000)
    IS_DELETED                                NOT NULL NUMBER(1)
    BL_SITE_ID                                NOT NULL NUMBER(38)
    ASSET_OBJECT_REF_ID                                NUMBER(38)

     

    *** Comment *** : we have manually updated the structure of this table : NAME VARCHAR2(2000) -> VARCHAR2(4000).

     

    SQL> desc I1$_D_CONFIG_FILE_OBJECT;
    Name                                      Null?    Type
    ----------------------------------------- -------- ----------------------------
    CONFIG_FILE_OBJECT_HIST_ID                         NUMBER(38)
    CONFIG_FILE_OBJECT_ID                              NUMBER(38)
    BLFILE_ID                                          NUMBER(38)
    PATH_ID                                            NUMBER(38)
    RULE_ID                                            NUMBER(38)
    RECORD_NUMBER                                      NUMBER(38)
    IS_LEAF                                            NUMBER(1)
    NAME                                               VARCHAR2(2000 CHAR)
    DATA_VERSION                                       NUMBER(38)
    COMMENTS                                           VARCHAR2(2000 CHAR)
    IS_DELETED                                         NUMBER(1)
    BL_SITE_ID                                         NUMBER(38)
    IND_UPDATE                                         CHAR(1 CHAR)

     

    Thanks for your help.

     

    Fabrice.

  • 3. ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    John O'Toole

    Hi Fabrice,

     

    On the bladelogic schema side, has the CONFIG_FILE_OBJECT.NAME column also been modified manually from 2000 characters to 4000 characters in the past?

     

    It seems like it has and this is the source of the problem. I checked the 7.6 schema creation script and this column should be 2000 characters:

     

    CREATE TABLE config_file_object(

        config_file_object_id    NUMBER(38, 0)     NOT NULL,

        blfile_id                NUMBER(38, 0)     NOT NULL,

        path_id                  NUMBER(38, 0),

        meta_checksum_hi         NUMBER(22, 0),

        meta_checksum_lo         NUMBER(22, 0),

        rule_id                  NUMBER(38, 0),

        record_number            NUMBER(38, 0),

        is_leaf                  NUMBER(1, 0),

        name                     VARCHAR2(2000),

        data_version             NUMBER(38, 0),

        comments                 VARCHAR2(2000),

        is_deleted               NUMBER(1, 0)      NOT NULL,

        CONSTRAINT XPKconfig_file_object PRIMARY KEY (config_file_object_id)

        USING INDEX

    TABLESPACE &2

    )

    TABLESPACE &1

    ;

     

    I also checked it in 8.1 and it is still 2000 there.

     

    So it looks like this NAME column was widened on the bladelogic side. However, ETL is not expecting this column to ever be more than 2000 characters so it is not able to handle it. Even if you widen the D_CONFIG_FILE_OBJECT on the BSARA_DW side it doesn't help because the staging table will always get regenerated with the column as 2000 characters.

     

     

    Do you have any idea why that column was widened and when?

     

    Thanks,

     

    John

  • 4. ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    fdussauchoy

    John,

     

    I just ask the new technical administrator of the platform (the old left in July)
    I hope to get an answer tomorrow (we are evening in Paris!).

     

    From my side, I've never modifiy the structure, except on the BSARA_DW side !

     

    Thanks,

     

    Fabrice.

  • 5. ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    John O'Toole

    What may have happened is that a similar error happened previously on the BBSA side.

     

    The correct solution then would have been to change the LENGTH_SEMANTICS on the problem table/column (too late changing it at the instance level at that point) from 2000 BYTE to 2000 CHAR.

     

    Instead though, the decision might have been taken to widen the column from 2000 to 4000. This may have "resolved" the BBSA issue but is now causing BDSSA issues.

     

    I'll talk to our DBA team on this. I think the best solution will be to look at the data on the BBSA  side to see how many values are greater than 2000 characters. Then possibly trim this data back to 2000 characters, restore the column back to 2000 CHARS and go from there.

     

    We'll examine that data closely though before doing anything.

  • 6. ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    fdussauchoy

    Hi John,

     

    To introduce myself a little more, i'm an "old" specialist of ORACLE,  who worked on this database since 1990

    So, i had the same idea to truncate the records that have more than 2000 characters (11 records), but i don't know the possible relationship between this field and other tables (non-public schema ), so i prefer effectively wait your feedback.

     

    Thanks a lot.

     

    Fabrice.

  • 7. Re: ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    John O'Toole

    Hi Fabrice,

     

    Lets definitely not truncate anything until we are sure that is the right thing to do.

     

    Can you run the following please:

     

     

     

    a)      As the bladelogic user:

     

    SELECT * FROM nls_database_parameters;

    Select max(length(NAME)) from CONFIG_FILE_OBJECT;

    Select CONFIG_FILE_OBJECT_ID, length(NAME) from CONFIG_FILE_OBJECT where length(NAME) > 2000 order by length(NAME) desc;

     

     

    b)      As the bsara_dw user:

     

    SELECT * FROM nls_database_parameters;

     

     

    Thanks,

     

    John

  • 8. Re: ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    fdussauchoy

    Hi John,

     

    Please find the answer to your questions below :

     

    As User BLADELOGIC :

     

    1* SELECT * FROM nls_database_parameters

    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               WE8ISO8859P1
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_RDBMS_VERSION              10.2.0.3.0

    20 rows selected.

     

    SQL> Select max(length(NAME)) from CONFIG_FILE_OBJECT;


    MAX(LENGTH(NAME))
    -----------------
                 3184

     

    SQL> SQL> Select CONFIG_FILE_OBJECT_ID, length(NAME) from CONFIG_FILE_OBJECT where length(NAME) > 2000 order by length(NAME) desc;


    CONFIG_FILE_OBJECT_ID LENGTH(NAME)
    --------------------- ------------
                  3747358         3184
                  3835466         3184
                  3750859         2859
                  3835444         2740
                  3769919         2740
                  3770708         2740
                  3749345         2740
                  3748694         2740
                  3747315         2740
                  3750894         2740
                  3753148         2144

    11 rows selected.

     

     

    As bsara_dw:

     

    1* SELECT * FROM nls_database_parameters

    PARAMETER                      VALUE
    ------------------------------ ----------------------------------------
    NLS_LANGUAGE                   AMERICAN
    NLS_TERRITORY                  AMERICA
    NLS_CURRENCY                   $
    NLS_ISO_CURRENCY               AMERICA
    NLS_NUMERIC_CHARACTERS         .,
    NLS_CHARACTERSET               WE8ISO8859P1
    NLS_CALENDAR                   GREGORIAN
    NLS_DATE_FORMAT                DD-MON-RR
    NLS_DATE_LANGUAGE              AMERICAN
    NLS_SORT                       BINARY
    NLS_TIME_FORMAT                HH.MI.SSXFF AM
    NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
    NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
    NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
    NLS_DUAL_CURRENCY              $
    NLS_COMP                       BINARY
    NLS_LENGTH_SEMANTICS           BYTE
    NLS_NCHAR_CONV_EXCP            FALSE
    NLS_NCHAR_CHARACTERSET         AL16UTF16
    NLS_RDBMS_VERSION              10.2.0.3.0

    20 rows selected.

     

    Thanks,

     

    Fabrice

  • 9. Re: ORA-12899: value too large for column "BSARA_DW"."I1$_D_CONFIG_FILE_OBJECT"."NAME" (actual: 2740, maximum: 2000)
    John O'Toole

    Hi Fabrice,

     

    Sorry for the delay in getting back to you on this. Can you run the following as the bladelogic DB user so we can see whats in these particular rows:

     

    Select CONFIG_FILE_OBJECT_ID, NAME from CONFIG_FILE_OBJECT where length(NAME) > 2000 order by length(NAME) desc;

     

    You will want to spool this to a file. Here I am actually pulling the NAME data and not just the length.

     

    Thanks,

     

    John