Re: blank maxtents value for temp

From: Richard Chen <qchen_at_snet.net>
Date: Fri, 01 Dec 2000 23:40:02 -0500
Message-ID: <3A287D22.7A660312@snet.net>

David Fitzjarrell wrote:

> In our last gripping episode TurkBear wrote:
> >
> > Is there sufficient space in the tablespace's data files...or on its
 mount
> > point...
> >
> > Richard Chen <qchen_at_snet.net> wrote:
> >
> > >Hello,
> > >
> > > This is for 8.1.6.1.0 on linux redhat 6.2.
> > >
> > >While trying to create an index I received
> > >
> > >ORA-01630: max # extents (505) reached in temp segment in tablespace
> > >
> > >so I tried to increase max_extents:
> > >
> > >alter tablespace temp default storage (maxextents unlimited);
> > >
> > >But the create index command still fails. Listing the value of
> > >max_extents shows
> > >that the value to be blank:
> > >
> > >SQL> select MAX_EXTENTS from dba_tablespaces where
> > >TABLESPACE_NAME='TEMP';
> > >
> > >MAX_EXTENTS
> > >-----------
> > >
> > >Is the tablespace corrupted? How to increase max_extents in another
 way?
> > >
> > >Thanks for any info.
> > >
> > >Richard
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
> >

>

> It would appear that TEMP is locally managed. Default storage
> parameters cannot be set for locally managed tablespaces, thus your
> null, or blank, value for MAX_EXTENTS. It would appear that your only
> course of action, should this be the case, is to drop and recreate the
> tablespace with a larger extent size (of course all users will need to
> be out of the database and you'll need to have the database mounted but
> not opened):

>

> drop tablespace temp including contents;

>

> create tablespace temp
> datafile '.....' size 5120M
> extent management local uniform size 10M;

>

> Apparently the existing extent size is a bit small for your
> transaction; if the existing tablespace is set to AUTOEXTEND and no
> extent size was specified the default value is 64K. If the tablespace
> is set for uniform extent sizes and, again, no size was specified the
> default is 1M. You may be able to reuse the existing datafile for the
> tablespace and simply increase the extent size presuming that 505
> extents of the new size will fit in the existing file:

>

> drop tablespace temp including contents;

>

> create tablespace temp
> datafile '<existing file name>' size <existing file size> reuse
> extent management local uniform size 10M;

>

> I hope this helps.

>

> --
> David Fitzjarrell
> Oracle Certified DBA

>

> Sent via Deja.com http://www.deja.com/
> Before you buy.

It was actually not related with TEMP. I misread the error message:

ORA-01630: max # extents (505) reached in temp segment in tablespace FOO

It is tablespace FOO whose max extents is reached. Once that is set to unlimited, creating index worked.

However, I did try to create a locally managed TEMP tablespace. But when I tried to create the index I got the error:

ORA-03212: Temporary Segment cannot be created in locally-managed tablespace

So locally managed tablespace does not work in this case.

Thanks for your help.

Richard Received on Fri Dec 01 2000 - 22:40:02 CST