Re: Reached limit on Temp Tablespace Extents

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
Oracle always uses the default storage specifications of the tablespace used for temporary storage when creating a temporary segment. So, if you would alter the default storage parameters of your TEMP tablespace, such that much larger extents are created, you wouldn't run into the limit of 121 extent (which is, by the way, the actual limit). for example:

alter tablespace TEMP default storage (initial 500K next 500K pctincrease 0);

woudl probably do the trick, assuming your tablespace TEMP is not yet completely fragmented (in that case, recreate the tablespace).

Hope this helps.

Marc. Received on Fri Oct 07 1994 - 14:39:21 CET