0

Summarize the problem I have been given 6 tables that have all been created already and I was told to change the storage parameters of those tables. the issue I run into is that I can only change pctfree, pctused, initrans and max trans and not the others (initial, next, pctincrease and maxextents)

Provide details and any research

I have done a lot of research, yet some of them do not work with sql developer at all. Whilst the others do work but as stated only for the 4 storage parameters.

When appropriate, describe what you’ve tried

alter table CUSTOMERS
pctused 20 pctfree 80;

This works perfectly for those two, but I am unable to add the others. From what I found online, this was in fact the only thing that worked for me.

I appreciate all input!

3
  • Well those four parameters you mention don't apply to tables, only to tablespaces.
    – user1822
    Sep 2, 2020 at 8:01
  • This helps me a lot, thank you so much. I am very new to this all.
    – DaleJV
    Sep 2, 2020 at 8:02
  • Sounds like a homework or interview question.
    – EdStevens
    Sep 2, 2020 at 11:58

1 Answer 1

1

The extent parameters can not be changed of an existing table, they are specified when a table is created. You can change them by reorganizing the table.

SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
     from user_tables where table_name = 'T1';

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
         65536     1048576  2147483645

SQL>  alter table t1 storage(initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1);
 alter table t1 storage(initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1)
                        *
ERROR at line 1:
ORA-02203: INITIAL storage options not allowed


SQL> alter table t1 storage(next 2097152 maxextents 1073741822 pctincrease 1);
alter table t1 storage(next 2097152 maxextents 1073741822 pctincrease 1)
*
ERROR at line 1:
ORA-25150: ALTERING of extent parameters not permitted


SQL> create table t2 storage (initial 1048576 next 2097152 maxextents 1073741822 pctincrease 1)
     as select * from t1;

Table created.

SQL> select INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, PCT_INCREASE
     from user_tables where table_name in ('T1', 'T2');

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE
-------------- ----------- ----------- ------------
         65536     1048576  2147483645
       1048576     2097152  2147483645

Notice how the MAX_EXTENTS and PCT_INCREASE parameter did not change. They are relevant only for dictionary managed tablespaces, but that was forgotten ages ago, we use locally managed tablespaces.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.