ORA-1450 in MAIN_TRANSEXEC/PARMVNT_TRANS phase

[Former Member]

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

[Former Member]

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

[achmad_dimyati5]

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

[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

[stefan_koehler]

Hi Roberto,

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

Regards

Stefan

[Former Member]

Thanks for the feedback.

Below the three tables where we have the error :

SQL> desc SAPSR3."/BI0/APA_DS0200";
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TCTUSERNM                                 NOT NULL VARCHAR2(36)
TCTAUTH                                   NOT NULL VARCHAR2(36)
TCTADTO                                   NOT NULL VARCHAR2(24)
TCTIOBJNM                                 NOT NULL VARCHAR2(90)
TCTSIGN                                   NOT NULL VARCHAR2(3)
TCTOPTION                                 NOT NULL VARCHAR2(6)
TCTLOW                                    NOT NULL VARCHAR2(3999)
TCTHIGH                                   NOT NULL VARCHAR2(3999)
TCTOBJVERS                                NOT NULL VARCHAR2(3)
TCTSYSID                                  NOT NULL VARCHAR2(30)
RECORDMODE                                NOT NULL VARCHAR2(3)
TCTADFROM                                 NOT NULL VARCHAR2(24)

SQL> descr SAPSR3."/BI0/ATCA_DS0100"
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TCTUSERNM                                 NOT NULL VARCHAR2(36)
TCTAUTH                                   NOT NULL VARCHAR2(36)
TCTADTO                                   NOT NULL VARCHAR2(24)
TCTIOBJNM                                 NOT NULL VARCHAR2(90)
TCTSIGN                                   NOT NULL VARCHAR2(3)
TCTOPTION                                 NOT NULL VARCHAR2(6)
TCTLOW                                    NOT NULL VARCHAR2(3999)
TCTHIGH                                   NOT NULL VARCHAR2(3999)
TCTOBJVERS                                NOT NULL VARCHAR2(3)
TCTSYSID                                  NOT NULL VARCHAR2(30)
RECORDMODE                                NOT NULL VARCHAR2(3)
TCTADFROM                                 NOT NULL VARCHAR2(24)

SQL> desc SAPSR3."/BI0/ACCA_O0100";

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

TCTUSERNM                                 NOT NULL VARCHAR2(36)

TCTAUTH                                   NOT NULL VARCHAR2(36)

TCTADTO                                   NOT NULL VARCHAR2(24)

TCTIOBJNM                                 NOT NULL VARCHAR2(90)

TCTSIGN                                   NOT NULL VARCHAR2(3)

TCTOPTION                                 NOT NULL VARCHAR2(6)

TCTLOW                                    NOT NULL VARCHAR2(3999)

TCTHIGH                                   NOT NULL VARCHAR2(3999)

TCTOBJVERS                                NOT NULL VARCHAR2(3)

TCTSYSID                                  NOT NULL VARCHAR2(30)

RECORDMODE                                NOT NULL VARCHAR2(3)

TCTADFROM                                 NOT NULL VARCHAR2(24)

Below the DLL statement for one of them:

2EETP334 14:48:22: error in DDL, nametab for "/BI0/ACCA_O0100" not activated

3 ETP379X14:48:22: activating Nametab "/BI0/APA_DS0200":

3 ETP355Xstatements:

3 ETP399 CREATE UNIQUE INDEX "/BI0/APA_DS0200~0" ON "/BI0/APA_DS0200"

3 ETP399 ("TCTUSERNM",

3 ETP399 "TCTAUTH",

3 ETP399 "TCTADTO",

3 ETP399 "TCTIOBJNM",

3 ETP399 "TCTSIGN",

3 ETP399 "TCTOPTION",

3 ETP399 "TCTLOW",

3 ETP399 "TCTHIGH",

3 ETP399 "TCTOBJVERS",

3 ETP399 "TCTSYSID")

3 ETP399 PCTFREE 10

3 ETP399 INITRANS 002

3 ETP399 TABLESPACE PSAPSR3

3 ETP399 NOCOMPRESS

3 ETP399 STORAGE (INITIAL 0000000016 K

3 ETP399 NEXT 0000002560 K

3 ETP399 MINEXTENTS 0000000001

3 ETP399 MAXEXTENTS UNLIMITED

3 ETP399 PCTINCREASE 0000

3 ETP399 FREELISTS 001)

3 ETP399

2WETP000 14:48:22: Retcode 1: error in DDL statement for "/BI0/APA_DS0200               " - repeat

2EETP345 14:48:34: Retcode 1: SQL-error "1450-ORA-01450: maximum key length (6398) exceeded" in DDL

2EETP345  statement for "/BI0/APA_DS0200               "

2 ETP399  --------------- DB-ROLLBACK() ---------------

2EETP334 14:48:34: error in DDL, nametab for "/BI0/APA_DS0200" not activated

3 ETP379X14:48:34: activating Nametab "/BI0/ATCA_DS0100":

regards

[stefan_koehler]

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