Skip to Content
avatar image
Former Member

ORA-1450 in MAIN_TRANSEXEC/PARMVNT_TRANS phase

Dears,

I have this error during a Netweaver 740 upgrade, on Oracle 11.2.0.3, for a BI sistem.

"2WETP000 15:18:51: Retcode 1: error in DDL statement for "/BI0/ACCA_O0100               " - repeat 2EETP345 15:19:03: Retcode 1: SQL-error "1450-ORA-01450: maximum key length (6398) exceeded" in DDL 2EETP345  statement for "/BI0/ACCA_O0100               "

The error happen to three tables /BI0/* and I'm not able to find a solution.

According the Oracle documentation, If I understood well I should reduce the size of the key of these tables, but It seems strange to me the SUM it's doing this error trying to create these indexes so bigger.

I'm not able to find any Oss Notes for that.

Any advice ?

regards

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • avatar image
    Former Member
    Jun 10, 2013 at 03:00 PM

    We noticed these three tables have two fiels with abnormal lenght:

    TCTLOW NOT NULL VARCHAR2(3999)

    TCTHIGH NOT NULL VARCHAR2(3999)

    We noticed into another Bw system these fields are  180 long.

    Then, as these tables are empy, and they are not used in our environment where we are doing the upgrade, and these tables are missing in production we did the following:

    we dropped these tables and recreated them with the fields with a lower size, as it was before the upgrade:

    TCTLOW NOT NULL VARCHAR2(180)

    TCTHIGH NOT NULL VARCHAR2(180)

    then restart the upgrade and these object were activated successfully.

    The upgrade went over the phase.

    When the upgrade it's going to be finished, we will drop again these

    tables, as they are not used .

    They have been activated for test purposes in this system years ago,

    but now they are obsolete.

    Regards

    Add comment
    10|10000 characters needed characters exceeded

  • Jun 07, 2013 at 05:39 PM

    Hi Roberto,

    please post the corresponding (full) DDL statements and the table definition of the three tables (describe <TABNAME>).

    Regards

    Stefan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Roberto,

      thanks for providing the information. The root cause is quite obvious here. Your unique index includes nearly all table columns (except RECORDMODE and TCTADFROM), but the issue is that it includes large columns like TCTLOW or TCTHIGH with max 3999 bytes.

      Oracle (in a SAP environment) uses "nls_length_semantics = bytes" by default and so in your case the index key can be round about 8316 bytes by definition. This is not allowed by design as you are using a default 8 kb block size.

      Oracle Documentation: http://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes002.htm#ADMIN11716

      The maximum size of a single index entry is approximately one-half the data block size.

      So in your case you would already hit this limit, if you include just one of these large columns. Raise a SAP SR for that as these tables are SAP standard afaik and so they need to re-check their table / index definition.

      Regards

      Stefan

  • avatar image
    Former Member
    Jun 09, 2013 at 07:21 AM

    Hi Roberto,

    It seems you have an issue related with IOT tables (Note 641435 - FAQ: Oracle Index
    Organized Tables (IOTs)
    and limitation of Oracle (ORA-1450).

    For starting point please adjust your Oracle parameters with Note 1171650 - Automated Oracle DB  parameter check and try again.

    Regarding the provided table, as I have checked fresh installation NW 7.4, there are no table with above name, so this tables is your current BW tables. You can consider to reorganize the table first to affecting the clustering factor of index creation (Note 832343 - FAQ: Clustering factor), hopefully it will reduce the number of leaf of B*Tree (related with ORA-1450).

    Last but not least, if above options aren't help, you can consider to empty the tables and populate again later.


    Regards.

    Achmad

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thanks for the feedbacks.

      I noticed these tables are empty in this development environment where the upgrade is in progress.

      SQL> select count(*) from SAPSR3."/BI0/ACCA_O0100";

      COUNT(*)

      ----------

      0

      SQL> select count(*) from SAPSR3."/BI0/APA_DS0200";

      COUNT(*)

      ----------

      0

      SQL> select count(*) from SAPSR3."/BI0/ATCA_DS0100";

      COUNT(*)

      ----------

      0

      And I noticed also these tables are missing in production environment.

      But as the Abap istance is down on the Development environment, I cannot check from SAP who is the developer responsible for these objects.

      Regards