How can I change FREELISTS permanently?

[Former Member]

I migrated a R/3 4.6C (oracle 9i) from AIX platform to Windows 2003 platform. it's 4.6C with oracle 10g database on Windows platoform. When I apply patch, I always get error of creating tables by SPAM.

Sample:
Retcode 1: error in DDL statement for DB6PMSA
Error 2168-ORA-02168: invalid value for FREELISTS when executing ADD-FIELD of

I have to go to SE14 to maintain storage parameters, set FREELISTS & FREELISTS GROUP as 1, (the previous value is 0). But I don't think I can modify it one by one as there are too many tables I have to change during one patch implementation.

What's wrong? Is there any solution to update FREELISTS & FREELISTS GROUP to 1 as default value and used for other tables creation during patch implementation.

Please advise, and thanks in advance.

James

[Former Member · Accepted solution]

This looks like the problem we had, when we were applying support packages. Please have a look at this note [627246 - Error when you create new objects. ORA-02168|https://service.sap.com/sap/bc/bsp/spn/sapnotes/index2.htm?numm=627246]

It should be possible to edit the tables tataf and ddstorage directly, to alter all object in one go.

Good luck, Michael

[Former Member · Accepted solution]

Follow up Note Note 627246, I did below, and the error changed.

update "TATAF" set prtext = 'FREELISTS 001' where prtext = 'FREELISTS 000';
   update "TATAF" set prtext = 'FREELIST GROUPS 01)' where prtext = 'FREELIST GROUPS 00)';
Correct the incorrect entries in the DDSTORAGE table:
   update "DDSTORAGE" set text = '1' where parname = 'FREELISTS';
   update "DDSTORAGE" set text = '1' where parname = 'FREELIST_GROUPS' 

The new error.

No DDL commands found for activation of DB6PMSB
02:04:45: Retcode 1: error in DDL statement for DB6PMSD
Error 2143-ORA-02143: invalid STORAGE option when executing ADD-FIELD of
Error  in DDL statement for when executing ADD-FIELD of

Any other advise? Thanks

James

[Former Member · Accepted solution]

Could you please look into the transport logs, and find the SQL statement that failed. The logs can be accessed from SPAM, then follow the red lines and expand the log until you see the SQL.

Regards, Michael

[Former Member · Accepted solution]

For your referece, please advise. Thanks.

 3 ETP399 CREATE TABLE "DB6PMSM"
3 ETP399 ("COMPTIME" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "PARTITN" NUMBER (000010)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "CALLERFLAG" VARCHAR2 (000001)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "DBMSTRTTME" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "PRGVERS" VARCHAR2 (000020)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "SRT_HP_ALO" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "PT_THSORTS" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "PPD_SRT_RQ" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "PPD_SRT_AC" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "DB2_STRTTM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "LAST_RESET" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "REM_CN_IN" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "REM_CN_INE" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "LOCAL_CONS" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "LOCL_CN_IE" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "CN_LC_DBS" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "AGT_REG" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "AGT_WAI_TK" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "UOW_SW" VARCHAR2 (000005)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "UOW_SWSTM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "STMT_SW" VARCHAR2 (000005)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "STMT_SWSTM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "TAB_SW" VARCHAR2 (000005)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "TAB_SWSTM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "BP_SW" VARCHAR2 (000005)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "BP_SWSTM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "LCK_SW" VARCHAR2 (000005)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "LCK_SW_STM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "SRT_SW" VARCHAR2 (000005)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "SRT_SWSTM" VARCHAR2 (000014)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "DB2_STATUS" VARCHAR2 (000025)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "AGT_REG_TP" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "AGT_WAI_TP" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "COMM_PIMEM" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "IDEL_AG" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "AGT_FR_PO" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "AGT_CR_EPO" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "CRD_AG_TOP" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "MX_AG_OF" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "AGT_STOLEN" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "NUM_SCDBMS" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "PROD_NAME" VARCHAR2 (000032)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "COMPO_ID" VARCHAR2 (000020)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "SVC_LEVEL" VARCHAR2 (000008)
3 ETP399 DEFAULT ' ' NOT NULL,
3 ETP399 "GW_TIT_CN" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "GW_CUR_CN" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "GW_CN_W_HT" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "GW_CN_W_CL" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL,
3 ETP399 "PT_THHAJN" NUMBER (000022, 000000)
3 ETP399 DEFAULT 0 NOT NULL)
3 ETP399 PCTFREE 10
3 ETP399 PCTUSED 00
3 ETP399 TABLESPACE PSAPT01
3 ETP399 STORAGE (INITIAL 0000000016 K
3 ETP399 NEXT 0000000000 K
3 ETP399 MINEXTENTS 0000000001
3 ETP399 MAXEXTENTS UNLIMITED
3 ETP399 PCTINCREASE 0000
3 ETP399 FREELISTS 001
3 ETP399 FREELIST GROUPS 01)
3 ETP399  
3 ETP356 executed ( "00:00:00" )
2 ETP399  -
 DB-COMMIT() -
3 ETP399 DELETE FROM DDSTORAGE WHERE
3 ETP399 DBSYSABBR = 'ORA' AND
3 ETP399 TABNAME = 'DB6PMSM' AND
3 ETP399 INDEXNAME = ' '
3 ETP399  
3 ETP356 executed ( "00:00:00" )
2 ETP399  -
 DB-COMMIT() -
3 ETP399 CREATE UNIQUE INDEX "DB6PMSM~0" ON "DB6PMSM"
3 ETP399 ("COMPTIME",
3 ETP399 "PARTITN",
3 ETP399 "CALLERFLAG")
3 ETP399 PCTFREE 10
3 ETP399 TABLESPACE PSAPT01
3 ETP399 STORAGE (INITIAL 0000000016 K
3 ETP399 NEXT 0000000000 K
3 ETP399 MINEXTENTS 0000000001
3 ETP399 MAXEXTENTS UNLIMITED
3 ETP399 PCTINCREASE 0000
3 ETP399 FREELISTS 001
3 ETP399  
2EETP345 01:56:58: Retcode 1: SQL-error "2143-ORA-02143: invalid STORAGE option
2EETP345 " in DDL statement for "DB6PMSM                       "
2 ETP399  -
 DB-ROLLBACK() -
2EETP334 01:56:58: error in DDL, nametab for "DB6PMSM" not activated

[Former Member · Accepted solution]

Sorry for my late response, got carried away in useless meetings.

Looks like you are missing a closing bracket ")" for the create index statement. I get the same error without the closing bracket. So please recheck the tables DDSTORAGE and TATAF for entries of DB6PMSM, especially TATAF for an entry like this:

TABNAME                        POSNR  PRTEXT 
DB6PMSM                           14  FREELISTS 001)

Might very well be, that the bracket is missing there.

Best regards, Michael

[Former Member · Accepted solution]

Thanks for your quick reply, It's solved based on your solution. The problem is when I implement another fix patch, I got same error, that is, FREELISTS & FREELIST_GROUPS have invalid value, how can I change them to 1 permanently and no error occurs when implement fix patch? Thanks in advance.

James

[Former Member · Accepted solution]

when I implement another fix patch

Generally the correction from sap note 627246 should be applied with transaction SNOTE before you import a support package, then you should be fine.

Regards, Michael

[Former Member]

still need check