cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-01630 message

hubert_1
Occasional Visitor

ORA-01630 message

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?






3 REPLIES
harry d brown jr
Honored Contributor

Re: ORA-01630 message

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
Live Free or Die
Brian Crabtree
Honored Contributor

Re: ORA-01630 message

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
Occasional Visitor

Re: ORA-01630 message

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.