Re: Reached limit on Temp Tablespace Extents

From: Stephen Laverack <devsl_at_cisa.bt.co.uk>
Date: Fri, 7 Oct 1994 12:57:11 +0000
Message-ID: <CxAzzC.GvK_at_cisa.bt.co.uk>


bgilst_at_gmuvax.gmu.edu wrote:
: Hi,
: My TEMP table space cannot extend any futher when I am trying to build an
: index. The first time it was at 99 extents, so I raised it to 500(I think the
: actual limit is 256). Now it stops at 122. I don't understand this. If
: nothing is permanently stored in the temporary tablespace, why doesn't it use
: all the space. The temporary tablespace is 5M and my data and index tablespaces
: are 10M each, so I feel I should have plenty of space. Can I defragment the
: temporary tablespace somehow to get rid of the extents(I couldn't find this in
: any book. Oracle error book says to add another datafile, Great). Can I just
: drop the TEMP tablespace and recreate it? Also, I am using Oracle Version 6,
: VMS, and no one else is running an Oracle transaction while I'm trying to build
: this index.
: Any suggestions would be appreciated
: Guy

The largest MAXEXTENTS you can specify, is entirely governed by your database block size, since the first block of each DB segment contains an extent map for each extent. For a 2K block size,you can have a maximum of 121 extents for any given object.

So short of increasing the database block size (not recommended) you cannot go beyond this.

Therefore, make your extents bigger!

In the temporary tablespace, alter the default storage clause settings for INITIAL, NEXT and PCTINCREASE. Any temporary object will use these storage settings, and therefore you should be able to use the entire temp tablespace if need be.

If you then get the error "Failed to allocate extent ..... in temp tablespace.."then you will need to increase the size of the tablespace itself

Steve Received on Fri Oct 07 1994 - 13:57:11 CET

Original text of this message