Re: blank maxtents value for temp

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.