topic Max Extent Reached in Operating System - HP-UX

Max Extent Reached

grace white — Tue, 30 Sep 2003 05:50:38 GMT

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

Re: Max Extent Reached

T G Manikandan — Tue, 30 Sep 2003 05:59:58 GMT

SQL>alter tablespace PMDB_NDX1 storage(maxextents unlimited);

Re: Max Extent Reached

Jean-Luc Oudart — Tue, 30 Sep 2003 06:01:34 GMT

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

Re: Max Extent Reached

grace white — Tue, 30 Sep 2003 06:14:43 GMT

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

Re: Max Extent Reached

T G Manikandan — Tue, 30 Sep 2003 06:26:56 GMT

SQL>alter tablespace default storage (maxextents unlimited);

Re: Max Extent Reached

grace white — Tue, 30 Sep 2003 06:31:04 GMT

Thanks

Thats worked
Great

Re: Max Extent Reached

Yogeeraj_1 — Tue, 30 Sep 2003 07:19:20 GMT

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

Re: Max Extent Reached

Jean-Luc Oudart — Tue, 30 Sep 2003 07:51:58 GMT

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