cancel
Showing results for 
Search instead for 
Did you mean: 

Max Extent Reached

SOLVED
Go to solution
grace white
Advisor

Max Extent Reached

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
UNIX/ORACLE DBA
7 REPLIES
T G Manikandan
Honored Contributor
Solution

Re: Max Extent Reached

SQL>alter tablespace PMDB_NDX1 storage(maxextents unlimited);
Jean-Luc Oudart
Honored Contributor

Re: Max Extent Reached

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
fiat lux
grace white
Advisor

Re: Max Extent Reached

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
UNIX/ORACLE DBA
T G Manikandan
Honored Contributor

Re: Max Extent Reached

SQL>alter tablespace default storage (maxextents unlimited);
grace white
Advisor

Re: Max Extent Reached

Thanks

Thats worked
Great
UNIX/ORACLE DBA
Yogeeraj_1
Honored Contributor

Re: Max Extent Reached

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Jean-Luc Oudart
Honored Contributor

Re: Max Extent Reached

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
fiat lux