ORA-01630 message

[hubert_1]

I currently facing a problem of this msg everytime I run a PL/SQL script:

ORA-01630: max # extents (99) reached in temp segment in tablespace TBS_USER02

select initial_extent, next_extent, max_extents
from user_tablespaces
where tablespace_name='TBS_USER02' ;

INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
-------------- ----------- -----------
40960 40960 50

The "max extents" value of TBS_USER02 is 50.

Increasing the value of MAX_EXTENTS doesn't
solve the error.

I hope you can provide me some info of, how should I approach this problem in order to solve it?

[harry d brown jr]

see

http://orafaq.net/msgboard/server/messages/12295.htm

or

ORA-01630: max # of extents num reached in temp segment in tablespace name

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.

Action: If possible, increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters or find the data dictionary table lacking space and alter the storage parameters as described in the Oracle8 Server Administrator's Guide.

@ http://storacle.princeton.edu:9001/oracle8-doc/server.805/a58312/newch225.htm

live free or die
harry

[Brian Crabtree]

This error normally happens as part of an index creation statement. What command is being run in your SQL statement that is causing this error? Can you post it?

Thanks,

Brian

[Peter van Rijn_1]

Using temporary segments in another tablespace than your temporary tablespace (usually called TEMP) is indeed usually caused by creating indexes.
The initial/next extent settings of the tablespace TBS_USER02 are quite low, and this will probably cause your error: teh max. number of extents that are allowed are consumed quite easily this way.

Try increasing the values to, let's say 1m:

alter tablespace tbs_user02
default storage(initial 1m next 1m maxextents unlimited);

If index creation is not the cause of this temporary segment I think the temporary tablespace of the user is not OK, you should use a 'real' temporary tablespace for sorting activities.