Re: Need help : problem with snapshot that take all the temporary space ...

From: Cong TANG <lai-tang_at_mycity.at>
Date: Wed, 01 Dec 1999 21:57:39 +0100
Message-ID: <38458BC3.75D1A986@mycity.at>

Perhaps the temporary segment did not shrink after previous sorts. Check the freespace of the TEMP tablespace before executing the CREATE SNAPSHOT statement. If you have 2GB of  freespace on the TEMP tablespace before executing this statement and the problem still occurs, you may need to simplify your CREATE SNAPSHOT statement.

mric wrote:

Hi,

I've to create a huge snapshot from several tables with many 'group by'
clauses and outer joins. So my query is relativly complex, and
when I try to create the snapshot I always have an ORA-01652 (unable to
extent temp segment by 12800 in tablespace temp).

So I've got a 2.5Go temp tablespace with initial an next extent equal to
25Mo. My sort area_size is 10Mo and sort_area_retained_size =
sort_area_size.

It's strange to have all the temporary space used by this creation. So my
question is : what can I do to create this snapshot in this conditions ???
Is my only solution to extend TEMP tablespace ??

I'm using Oracle 7.3.4.0.1 on a Sparc Solaris 2.6.

thanks in advance,

    Aymeric Barantal (mric)
    dba @ grolier interactive europe

Received on Wed Dec 01 1999 - 14:57:39 CST