Max Extent Reached

[grace white]

Hi

I have reached my Max extent limit of 121 and need to know how to alter this.

select t.initial_extent,t.next_extent,t.min_extents,t.max_extents,t.pct_increase
from dba_tablespaces t
where t.tablespace_name = 'PMDB_NDX1';

Could anyone help me out.

thanks
grace

[T G Manikandan · Accepted solution]

SQL>alter tablespace PMDB_NDX1 storage(maxextents unlimited);

[Jean-Luc Oudart]

ALTER TABLE STORAGE ( MAXEXTENTS 400);

example to increase the number to 400.
You may want to move to LMT so you would not worry about it !

Rgds,
Jean-Luc

[grace white]

Hi

I have tried the alter tablespace PMDB_NDX1 storage(maxextents unlimited);
and got the following message:
SQL> alter tablespace PMDB_NDX1 storage(maxextents unlimited);
alter tablespace PMDB_NDX1 storage(maxextents unlimited)
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

Can you please advise

also you mention the ALTER TABLE STORAGE how do i find the table name.

Thanks
Grace

[T G Manikandan]

SQL>alter tablespace default storage (maxextents unlimited);

[grace white]

Thanks

Thats worked
Great

[Yogeeraj_1]

hi,
*
If you have Oracle 8i, you should start using LOCALLY MANAGED tablespaces.
*
With locally managed tablespaces and you teach yourself to never use
*
o initial
o next
o minextents
o maxextents
o pctincrease
*
A locally managed tablespace with uniform extents "de-facto" rule totally avoids fragmentation -- it is impossible to have fragmentation in these tablespaces.
*
Locally managed tablespaces are much faster at allocating and de-allocating extents - many order of magnitudes faster.
*
Locally managed tablespaces decrease contention on the data dictionary.
*
You will never have such problems again!
*
Best regards
Yogeeraj

[Jean-Luc Oudart]

Grace,

if you want to know more about Locally managed tablespaces (LMT) you can check this previous thread.

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=50515

Regards,
Jean-Luc