I was working on a maintenance pack application to a finance environment the other day and ran into a problem when doing the alter builds.  When building a table I received the ORA-02221 error: invalid maxtents storage option value.  It would appear that at some time in the past an upgrade occurred where the setindex and settable SQR’s ran.  These SQR’s will set the maxextents override incorrectly in a specific situation. The situation is if the USER_INDEXES.MAX_EXTENTS is null in the database.

The end result is  an override of zero being stored in PSIDXDDLPARM and PSRECDDLPARM.  The DDL later generated by Application Designer, to create tables and indexes,  will include a “MAXEXTENTS 0” clause for the index.  This is an illegal clause for the Oracle database platform.  The MAXEXTENTS parameter should be unlimited and not set to zero.

Fix 1:

Delete from PSRECDDLPARM where PARMNAME = ‘MAXEXT’ AND PARMVALUE = 0;
Delete from PSIDXDDLPARM where PARMNAME = ‘MAXEXT’ AND PARMVALUE = 0;

Fix 2: Alternately, run the following sql:

UPDATE PSRECDDLPARM SET PARMVALUE = 2135468 WHERE PARMVALUE = 0;
UPDATE PSIDXDDLPARM SET PARMVALUE = 2135468 WHERE PARMVALUE = 0;