ORA-1630: max # extents 75 reached in temp segment for tablespace INVX

[ggnanaraj]

The error is

ORA-1630: max # extents 75 reached in temp segment for tablespace INVX


Cause: The maximum amount of space allowed for saving undo entries has been reached for a temporary segment in the named tablespace, or space could not be
allocated in the data dictionary to contain the definition of the object.

How do I rectify this?

Thanks.

With regards.

[pipo]

increase maxextents for your tablespace :
alter tablespace INVX default storage (maxextents 100);

[ggnanaraj]

I have quite a lot of MAX_EXTENTS

TST > select max_extents from dba_tablespaces where TABLESPACE_NAME LIKE 'INVX';

MAX_EXTENTS
-----------
2147483645

1 row selected.

So, what is the problem?

Thanks.

[pipo]

what is the value of the contents column from dba_tablespaces ?? your tablespace should be in temporary mode, else just look at the maxextents of the segment which can not extend

[yotot]

Check the pctincrease, and raise the pctincrease for the tablespace

[rohitsn]

Hi,
There are 2 options for resolving this problem.

Solution 1: Shutdown the database with normal,immediate
option so that all the temporary segments will be
released.

Solution 2: Add a datafile to the temporary tablespace.

In case of any help please be free to ask me at rohitsn@altavista.com

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

[pipo]

you do not need to shutdown the databse to free temp tablespace !!!
and adding a datafile will not be useful since this one is not full, message if that maxextents has been reached, not that segment cannot allocate n bytes in file ... so tablespace should not be full, but the segment just cannot grow

[ggnanaraj]

Thanks for the replies.

The info requested is as follows...


TST > select max_extents, contents, pct_increase from dba_tablespaces where TABLESPACE_NAME LIKE 'INVX'

MAX_EXTENTS CONTENTS PCT_INCREASE
----------- --------- ------------
2147483645 PERMANENT 50

1 row selected.

Regards.

[pando]

Originally posted by yotot
Check the pctincrease, and raise the pctincrease for the tablespace

Absolutely NOT! Always set pctincrease 0, specially temporary tablespaces

[justadba]

What's the size of the tablespace, and what is the size of the initial and next extent for the tablespace. Could these values be large enough that you quickly run out of space?

ie; With a 1 Gbyte temp tablespace and initial and next extents set at 20 Mbytes and pctincrease of 50, you would run out of space in the tablespace after your ninth extent was created. And your tenth extent would want apprix 512 Mbytes of contigous space. So you can see the dangers of the pctincrease setting.