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?
- 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
- 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.
- 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
Received on Wed Mar 07 2007 - 09:41:45 CST
