unable to extend temp segment by 128 in tablespace temp

[pst]

Hi

I encountered this error when I was trying to compute statistics for a set of tables.
How do I go about this ?

All help appreciated.

Thanks a lot

pst.

[pando]

extentd the datafile size that blongs to temp tablespace

[sreddy]

Seems lot of sorting activity goin on your temp tablespace and your temp table space is full. try increasing the temp tablespace by resizing/adding datafile. Try coalesing temp tablespace.

SQL>alter tablespace TEMP coalesce ;

And see the contents from dba_tablespaces whether its temporary/permanent. it should be temporary.

SQL> SELECT CONTENTS FROM DBA_TABLESPACES where TABLESPACE_NAME='TEMP';

[pst]

If I do a query to give me the free space available in temp, this is the result I get.


TABLESPACE_NAME TOTAL_SPACE FREE_SPACE PCT_FREE
------------------------------ ---------------- ---------------- --------
TEMP 2,044,370,944 302,333,952 14.79

There seems to be space.

And also, this analyze I'm doing right now is for about 100 tables with some tables empty and some 6-7 million rows and others in the thousands range. This is really taking a lot of time, I started about 12.00 and now it's 3.30 and still it has'nt completed. Is this normal or is there anything I need to that that I've missed out ?

Thanks a lot

pst

[sreddy]

There could be free space, do you have 128 size chunk in that tablespace. If you do, you shouldn't get error. Seems its fragmented. try coalescing it and see that helps.

[pst]

Can I do the alter tablespace temp coalesce while the temp tablespace is used ?

[pss]

You should extend space for your rollbacks.

Sergey.