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

In article <838j8q$a4l$1_at_nnrp1.deja.com>,   Brian Yan <by999_at_hotmail.com> 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?
>
> Thank you in advance!
>
> Brian
>

You need to add table storage parameters to your SQL to eliminate running out of extents as your table is using 10K extents and depending on your system uet$ is probably larger than 121 10K extents.

Until your SQL completes sucessfully the extents allocated to extents_bk are help by Oracle as a temporary allocation which is not the same as a sort segment from a usage point of view. Only upon successful completion of the table creation are the extents recorded as being part of the new table.

Note a maxextents of 121 on the tablespce implies an Oracle block size of 2K. I would recommend you consider rebuilding the database using a 4K Oracle block if this is true and this is or is going to be a production system.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Dec 15 1999 - 12:58:10 CST