Extend MAX EXTENT

[uadmin]

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.

when i issue a alter tablespace PMDB_NDX1 storage(maxextents unlimited);

it gives me the following :

alter tablespace PMDB_NDX1 storage(maxextents unlimited)
*
ERROR at line 1:
ORA-02142: missing or invalid ALTER TABLESPACE option

any help please

simon

Simon Peter Wickham
Email: s.wickham@zoom.co.uk

 

[alexhu]

I would say it should be 'default storage' however you may then get

alter tablespace users default storage(maxextents unlimited)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

oerr ora 25143
25143, 00000, "default storage clause is not compatible with allocation policy"
// *Cause: default storage clause was specified for a tablespace with
// AUTOALLOCATE or UNIFORM policy
// *Action: Omit the storage clause



Alex

 

[DBAwhosaysNIE]

if you've hit max extents on an existing object (table, index, lob segment, etc.) changing the tablespace default isn't going to help you. you'll have to do it object by object. ex:

alter table s_emp storage(maxextents unlimited);

or if you want to do them all in one shot:

select 'alter table '||table_name||' storage(maxextnets unlimited;' from dba_tables where tablespace_name='USERS';
-- tweak as needed

 

[SantaMufasa]

Simon,

This is mainly a message of concurrence with Alex and 'Nie':

If you have encountered the error message, &quot;ORA-01631: max # extents (121) reached in table <tablename>,&quot; then changing the tablespace default-storage parameters is shutting the gate after the horse is already gone. To protect against this problem at the tablespace level, for FUTURE table and index creates, then you can take advantage of tablespace defaults with this command:

&quot;ALTER TABLESPACE <ts_name> PMDB_NDX1 default storage(maxextents unlimited);&quot;

Even then, you can effect this command only if the tablespace is NOT locally managed, autoallocate tablespace (which I recommend using).

To resolve this problem for EXISTING tables and indexes, you can cut, paste, save, and execute the following script (similar to &quot;Nie's&quot; except that it includes indexes as well as tables, and it closes all open parentheses <smile>), which should resolve your MAXEXTENTS problem for all the tables and indexes in your entire database (except for SYS and SYSTEM objects, which I specifically excluded):

set echo off
set pagesize 0
set trimspool on
spool temp.sql
select 'alter '||segment_type||' '||owner||'.'||segment_name||' storage (maxextents unlimited);'
from dba_segments
where segment_type in ('TABLE','INDEX')
and owner not in ('SYS','SYSTEM')
/
spool off
prompt
prompt Wrote file, &quot;temp.sql&quot;. To execute, &quot;SQL> @temp.sql&quot;
prompt

Cheers,

Dave

 

[sem]

And finally to make it in one shot:

begin
for f in(select 'alter '||segment_type
||' '||owner||'.'||segment_name
||' storage (maxextents unlimited)'
statement
from dba_segments
where segment_type in ('INDEX','TABLE','CLUSTER')
and owner not in ('SYSTEM','SYS')) loop

execute immediate f.statement;
end loop;
end;

Regards, Dima