Re: Newbie - Need help increasing tablespace..

Ok,
This does provide a few clues what happened but probably not sufficient. Usually the fastest solution (extending the tablespace) is not always good, so we need to delve a little bit deeper. Regrettably there is no single 'easy' solution as this problem can have various causes and the easy solution usually resulting in waste of disk space.

First of all, apparently the temporary tablespace of the user running the query is BTS1_7NS3. I don't know what's in that tablespace, usually it is better to dedicate one specific separate tablespace to temporary segments. Then of course, as a result of the error the query aborts and the temporary segment is dropped. That's why you are seeing free space in that tablespace. The error message says: I can't find 1598 contiguous BLOCKS (this has always been one of the few places where Oracle terminology keeps referring to blocks). Question is then: what was the size of the temporary segment before this happened. It seems that 44541952 bytes is not sufficient. Keep in mind: the storage parameters of a temporary segment are always taken from the default storage clause of the tablespace.

What you will probably need do to do is monitor the growing temporary tablespace as the query is running (this is quite easy: start a different session and repeat select segment_name, bytes from dba_segments where segment_type = 'TEMPORARY' and you will see the segment growing.

I would advise create a separate tablespace create tablespace temp
datafile '<filename> size 50M reuse
default storage (initial 500k next 500k ) temporary
making sure all users use this tablespace for their temporary segments by issuing
alter user <username> temporary tablespace temp;

Alternatively you can decide to change the default storage clause of the tablespace, so the biggest temporary segment will always fit.

Hth,
Sybrand Bakker, Oracle DBA
bmolish_at_my-dejanews.com wrote:

> Our DBA has left, and I am the new admin person..
> We are using Oracle 7.34 on a NT 4.0 box.
>
> Currently when I run a SQL query I am getting the following error..
>
> ORA-01652: unable to extend temp segment by 1598 in tablespace BTS1_7NS3;
>
> When I check out the tablespace this is what I get..
> By running the following..
>
> select
> tablespace_name,
> sum(bytes),
> max(bytes),
> count(*)
> from dba_free_space
> group by tablespace_name
> order by tablespace_name;
>
> TABLESPACE_NAME SUM(BYTES) MAX(BYTES) COUNT(*)
> ------------------------------ ---------- ---------- ---------
> BTS1_7NS3 44541952 40589312 5
> SYNCHRO_7NS3 5855232 5640192 3
> SYSTEM 10455040 10455040 1
>
> What is the exact command that I need to correct this error..
>
> Thanks..
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Thu Sep 24 1998 - 13:45:53 CDT