cancel
Showing results for 
Search instead for 
Did you mean: 

NEXT EXTENT size for the table

Former Member
0 Kudos

Hi All,

When checked in se14 for the table BPTIME_BP1030 the NEXT EXTENT size is 20480 which is considerably higher.The total size of the database is itself is around 55 gb.I am not able to reduce the size of the next extent of the table.Due to which i am getting a error during sp import.Please dont consider as cross post since i had raised a post in NWA for the sp error.I am not quite sure of the error.Its just a assumption.Please correct me if i am wrong.Thanks.

3 ETP399 CREATE UNIQUE INDEX "BPTIME_BP1030~0" ON "BPTIME_BP1030"

3 ETP399 ("CLIENT",

3 ETP399 "GUID_K")

3 ETP399 PCTFREE 10

3 ETP399 INITRANS 002

3 ETP399 TABLESPACE PSAPBAL

3 ETP399 NOCOMPRESS

3 ETP399 STORAGE (INITIAL 0000000000 K

3 ETP399 NEXT 0000000000 K

3 ETP399 MINEXTENTS 0000000000

3 ETP399 MAXEXTENTS 0000000000

3 ETP399 PCTINCREASE 0000

3 ETP399 FREELISTS 001)

3 ETP399

2WETP000 18:56:47: Retcode 1: error in DDL statement for "BPTIME_BP1030 " - repeat

2EETP345 18:56:59: Retcode 1: SQL-error "2220-ORA-02220: invalid MINEXTENTS storage option value" i

2EETP345 n DDL statement for "BPTIME_BP1030 "

2 ETP399 -


DB-ROLLBACK() -


2EETP334 18:56:59: error in DDL, nametab for "BPTIME_BP1030" not activated

2 ETP301 -


3 ETP361 "0" Shadow-Nametabs activated, DDL executed

2 ETP362 "2" Shadow-Nametab activations failed

Accepted Solutions (1)

Accepted Solutions (1)

audunlea_hansen
Active Participant
0 Kudos

Hi!

You can do an

alter table <tablename> storage ( next 1M minextents 2 maxextents UNLIMITED pctincrease 0);

minextents needs to be a number >= 1.

After creating the table/index, you are not allowed to change initial extent.

By the way, most of the databases havve tablespaces set up with extent management=local and a uniform size of 1M Due to this, Oracle would create needed extents with size = 1M until it past the size you set next extent in table to be..

Eg you set next = 10M and uniform size = 1M, the db creates 10x1M extents for you.

pctincrease should be set to = (zero)

maxextents should be a number larger than 254 (default) either unlimited or 1024 / 2048

Regards

Audun

DBA

Former Member
0 Kudos

Hi Audun,

Thanks a ton for response.What do you reckon with the error we have got.Thanks.

audunlea_hansen
Active Participant
0 Kudos

Hi!

From what I can read from your logs, you should change storage parameters for the table and indexes you got error reported

I would try in sqlplus as the user owning the table and indexes:

alter [table|index] <table_name | index_name> storage (next 1M minextents 2 maxextents UNLIMITED pctincrease 0);

If it's a big table, consider if you want next increased to 10M.

If your initial extent are too big, eg 400M, you can se problems allocating space when doing export/import or client|system copy.

Regards

Audun

former_member204746
Active Contributor
0 Kudos

Permanent solution:

convert your tablespace in locally managed mode.

look at SAP note 646681 to learn how to reorg your tablespaces in LMTS mode.

Answers (1)

Answers (1)

sunny_pahuja2
Active Contributor
0 Kudos

Hi,

Check SAP Note 1536104 - ORA-02220: invalid MINEXTENTS storage option value for your problem.

Thanks

Sunny

Former Member
0 Kudos

Hi Sunny,

Thanks for the response.Have done as mentioned in the note.We are having problems activating it.Its throwing up the below error.Thanks.

ORA-02220: invalid MINEXTENTS storage option value

DDL time(___3): .........1 milliseconds

The SQL statement was not executed

Statements for Table BPTIME_BP1030 could not be executed

sunny_pahuja2
Active Contributor
0 Kudos

Hi,

Have you dropped the 2 indexes as mentioned in the note ?

Thanks

Sunny

Former Member
0 Kudos

Hi,

One more thing forgot to mention database 11g.

Have done as mentioned.Please let me know if we can anything else.Thanks.

SQL> create table sapbal.user_indexes_backup tablespace psapbal as select * from sapbal.user_indexes;

Table created.

SQL> commit;

Commit complete.

SQL> drop synonym sapbal.user_indexes;

Synonym dropped.

.

SQL> commit;

Commit complete.

SQL> drop view sys.sap_user_indexes;

View dropped.

SQL> commit;

Commit complete.