Re: How to get rid of this error??

On Mar 7, 9:41 am, "sybrandb" <sybra..._at_gmail.com> wrote:
> On Mar 7, 4:22 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > Help, please! This error is occuring when I run a SELECT statement
> > with a DISTINCT clause in it. Without the DISTINCT clause, it runs
> > fine.
>
> > ERROR at line 2:
> > ORA-03232: unable to allocate an extent of 127 blocks from tablespace
> > 3
>
> > Tablespace #3 is actually the RBS segment. We are running 8.1.7.
> > I've tried setting autoextend, changing the size of the next extent
> > parameter......nothing works.......
>
> > Ideas anyone?
>
> 1) Review the statement to find out whether you really need the
> DISTINCTs. DISTINCTs are usually just a kludge to hide either the
> design is bad, or the developers don't know SQL or both
> 2) Realise DISTINCT implies SORT. Sorts are being executed in the
> temporary tablespace. This means the temporary tablespace for the user
> executing the statement has been set to the rollback tablespace.
> That is a really bad idea, as rollback and temp segments will now
> compete for resources.
> FIX IT, asap.
> 3) If you can't fix the messy statement, at least fix the messy
> database, and start reading those manuals instead of misusing this
> forum as a free of charge consultancy firm.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Checked your thoughts. TEMP is the default temp tablespace for this user. Also, I've put autoextend on, and increased the next parameter to 10M for both the TEMP and RBS tablespaces/datafiles......

No go........I'm frustrated. And, this is the only query I'm having issues with.

I might add, that it joins 2 LARGE tables, each table has roughly 2 million rows, so there is a big sort there. But, with a tablespace that has autoextend on, it should not be a problem........ Received on Wed Mar 07 2007 - 10:08:26 CST