Re: Temp TS was too small, but how much

"Marcel Kraupp" <marcel.kraupp_at_gmx.ch> wrote in message news:332bb004.0205300823.732c2e1c_at_posting.google.com...
> Hello
>
> 8i/Solaris
>
> I have a pl/sql procedure which basically iterates over
> a cursor like
>
> open r for (select a, b, c from LARGE_TABLE order by a, b)
>
> because its a large table and oracle needs to sort a and b
> first, oracle doesn't have enough temp table space and doesn'
> finish the procedure.
>
> Unfortunately, I have no idea how much bigger the temp tablespace
> should be. If, for example, only 200 MB more were needed, I'd
> gladly give it to the database. But if it needed 12 GB more, that'd
> be a different story.
>
> So, is there any possibility to determine (or estimate) the table
> space needed for an order by (or any other sort operation)?
>
> Thanks
> MK

It used to be
1.0 - 2.0 * the number of keys to be sorted * the sum of the average length of the individual keys.

Disk is usually, BTW, cheap.
If you think disk is expensive you probably need to decide whether you really want the result ordered.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address