Re: ora-1530: max # extents (121) reached??

Brian Yan wrote:

> 
> Hi there,
> 
> I got the following error when I tried to create a table:
> 
> SQL> create table extents_bk as
>   2  select * from extents;
> select * from extents
>               *
> ERROR at line 2:
> ORA-01630: max # extents (121) reached in temp segment in tablespace
> TS_MNTR
> 
> I ran this command in user MONITOR, whose default tablespace is TS_MNTR,
> temporary is TEMP.
> 
> The information about these two tablespaces are as follows,
> 
> CREATE TABLESPACE TEMP
>     DATAFILE '/u02/ORACLE/DSNR/temp01.dbf' SIZE 20480K AUTOEXTEND OFF
>     DEFAULT STORAGE(INITIAL 256K
>                     NEXT 256K
>                     MINEXTENTS 1
>                     MAXEXTENTS 121
>                     PCTINCREASE 0)
>     ONLINE
>     TEMPORARY;
> 
> CREATE TABLESPACE TS_MNTR
>     DATAFILE '/u05/ORACLE/DSNR/mntr_01.dbf' SIZE 102400K AUTOEXTEND OFF
>     DEFAULT STORAGE(INITIAL 10K
>                     NEXT 10K
>                     MINEXTENTS 1
>                     MAXEXTENTS 121
>                     PCTINCREASE 0)
>     ONLINE
>     PERMANENT;
> 
> My question is: why does Oracle complain temp segment in TS_MNTR? Since
> I have defined the temporary tablespace for user MONITOR, isn't it
> supposed to use temp segment in TEMP instead of TS_MNTR? How to fix this
> problem?

I've struggled with this issue and as best as I can determine the error message is simply wrong - or at least it is worded in a way that doesn't make sense in an intuitive manner.

In any case, the solution is to specify explicit storage parameters for your table rather than accepting the default storage parameters of the tablespace. 10K is way too small for large tables and I'd also say that 256K is too small for temp segments.

Does your database have database blocks of 2048? If not, and if you actually have 4096 blocks then change the maxextents for your tablespaces and objects to 249.

..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
..............................................................................
Received on Wed Dec 15 1999 - 11:51:14 CST