'temp' tablespace out of space?????

[chwdba]

I have several user queries that are got the the following error message:

ORA-1652: unable to extend temp segment by 256 in tablespace xxx

However in viewing and recording the tablespace's usage, I NEVER see it even getting close to be 100% used. The tablespace spans several datafiles and is 5.4 GB in total size.

Is the 'temp' tablespace limited to single file allocations? Any other ideas???

Thanks!!!

[sreddy]

How long the database is up and running ? There may be fragmentation causing the transaction fail to get 256 kb chunk.

Temp tablespace is not limited to single file allocation.

I don't think its possible to flush temp tablespace on the fly other than bouncing database which release previously used segments. Have a look at previous threads on TEMP tablespaces maintenance.

http://www.dbasupport.com/forums/sho...?threadid=7722
http://www.dbasupport.com/forums/sho...?threadid=6771
http://www.dbasupport.com/forums/sho...?threadid=5600

[marist89]

Time out. I don't think he's talking about the TEMP tablespace.

ORA-1652: unable to extend temp segment by 256 in tablespace xxx

indicates there is not enough space in XXX, not TEMP.