Re: Reached limit on Temp Tablespace Extents

From: Carl Gohringer <cgohring_at_lucifer>
Date: Fri, 7 Oct 1994 12:28:46 GMT
Message-ID: <CxAynz.K88_at_nl.oracle.com>


bgilst_at_gmuvax.gmu.edu writes:
: 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

The maximum number of extents which may be allocated to a segment is a function of the block size. For a database with a 2K block size, the maximum number of extents that may be allocated to a segment is 121. What you need to do is modify the DEFAULT STORAGE definitions for that tablespace. When the temporary segments are created in that tablespace, they will be created with the tablespace's DEFAULT STORAGE parameters. By specifying a larger INITIAL and NEXT sizes, the segments will have larger extents, and hence not as many extents will be reqquired.

regards,
Carl

+-----------------------------------------------------------------------------+
Carl Gohringer, European Development Centre Oracle Park, Bittams Lane, Chertsey, Surrey, England, KT16 9RG Internet : cgohring_at_uk.oracle.com
+-----------------------------------------------------------------------------+
Received on Fri Oct 07 1994 - 13:28:46 CET

Original text of this message