side effects of ORA-1652 unable to extend temporary tablespace

Hi,

Recently I got the following error in my alert.log file

ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORARY

My temproray tablespace is 300M and is Localy managed with uniform extents of 1M each.

How will this effect the database or the query/statement because of which this error occurred.

Should I just keep on increasing the tablespace till this error goes away even if the temp tablespace has to be of 10G. I mean how much is enough. How do I find out.

How to size the temporary tablespace.

Also is there a way to find out which statement triggered this error?



Please suggest.

Thanks

[Edited by ronnie on 06-13-2002 at 05:28 PM]

Size requirements are purely based on your requirements.

Keeping it simple TEMP depends on you Table Sizes.
Then it varies for what your are doing -- unions, joins, group by, order by ...

Apart from these temp is used when you create indexes.

If your are doing complex queries with multiple joins and group by's on tables with millions of rows, then you really need big TEMP.

I went upto 6 GIG for a TEMP before

There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...

Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..

Quote:

---

Originally posted by sreddy
There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...

Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..

---

and how harmful is this error for the database and what what will happen to the statement which triggered it.

sreddy can you please let us know about the issues with Temo LMT because I recently changed all my TEMP's to LMT's from Dictionary managed.

Also I have a couple of databases where RBS is LMT and they alsi seem to be working fine. Any thing which I should look for...

Thanks

Quote:

---

Originally posted by: sreddy
There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...

Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..

---

I too never heard of TEMP having issues with LMT.
LMT support RBS and from version 9 it supports SYSTEM as well.

Sanjay

I should have been little specific about the version I'm talking. Iam talking about LMTs on 8i.

I had little weird experience on creating TEMP tablespace as LMT... I can't see that tablespace in the list when I query dba-tablespaces view...

9i LMTs support all types of tablespaces... as SANJAY say. I'm not sure about the oracle version you are talking about ..

This error is not going to harm your database anyway, if it can't extend TEMP tablespace. Probably your query will not come back OR if someone trying run a complex query this error might get repeated. You don't need to be panic...

Quote:

---

Originally posted by sreddy
I had little weird experience on creating TEMP tablespace as LMT... I can't see that tablespace in the list when I query dba-tablespaces view...

---

Well, I see mine when I run select * from dba_tablespaces; :-)

Quote:

---

LMT support RBS and from version 9 it supports SYSTEM as well.

Sanjay

---

Only from 9.2!

LMT RBS is supported since 8i but since it had many bugs.....

Quote:

---

Originally posted by ronnie
Hi,

ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORARY

[Edited by ronnie on 06-13-2002 at 05:28 PM]

---

Have you set MAXSIZE for temporary tablespace?

Quote:

---

Originally posted by Calvin_Qiu

Quote:

---

Originally posted by ronnie
Hi,

ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORARY

[Edited by ronnie on 06-13-2002 at 05:28 PM]

---

Have you set MAXSIZE for temporary tablespace?

---

My temp tablespace cannot grow automatically. The DBA has to explicitly increase the size of the databafile.