TEMP tablespace grows all the time!

[jgmagnus]

Originally posted by jmodic

Originally posted by jgmagnus
So why did Julian say coalesce will not work?
Am I missing something here?
I don't get errors coalescing with an 8.1.7 db ...

But nevertheless, is simply doesn't make any sence at all to coalesce a temporary tablespace, no matter of the Oracle release or the flavor of your temp tablespace (if it realy is created as a temporary tablespace, which became available sometime arround 7.2 or 7.3, if I'm not mistaken). You are absolutely not gaining anything by coalescing temp tablespace, you are only slowing down your future temp space allocations.

thanks for the information.

so, what the heck is Tamil talking about then?

[tamilselvan]

To jgmagnus

I have a table with 120 Million rows. Analyze the full table for all indexed columns needs a very big temp tablespace, and if I created the temp tablespace with auto extent option, then I need 20 GB data files for the temp tablespace alone, which I do not want to do.

Besides huge space, analyzing 120 million rows is a time consuming job. I do not have that much window.

That is why I said, analyze only 5 % or even 1 % of the rows in a big table, so that growth of temp tablespace is controlled.
I hope you understand it.

[julian]

Tamilselvan is right. Actually, the only times I have run into a problem with the TEMP tablespace is when I have tried to analyze tables "100%".

[jmodic]

I think jgmagnus is talking about tamilselvan's suggestion about coalescing temp tablespace....

[julian]

Originally posted by jmodic
I think jgmagnus is talking about tamilselvan's suggestion about coalescing temp tablespace....

I see, well, coalescing temp tablespace is a meaningless concept.

[jgmagnus]

Originally posted by julian

Originally posted by jmodic
I think jgmagnus is talking about tamilselvan's suggestion about coalescing temp tablespace....

I see, well, coalescing temp tablespace is a meaningless concept.

Perhaps it is meaningless, but Tamil also suggested doing it. That was why I was asking about it. Tamil always knows what he (he?) is talking about so that is why I was curious.

- Magnus

[tamilselvan]

I never coalesce my temp tablespace in a normal working environment.
But if I encounter a problem with my TEMP tablespace, then I do coalesce it to ensure that my next extent allocation for the new sort will not fail.

In the past we had many problems with TEMP tablespace, when ever we do parallel index creation and analyze big tables and indexes. Those who had experience with "ANALYZE" command on a big table (100 million rows) on HP machines can understand what I am talking.

[julian]

Originally posted by tamilselvan
I never coalesce my temp tablespace in a normal working environment.
But if I encounter a problem with my TEMP tablespace, then I do coalesce it to ensure that my next extent allocation for the new sort will not fail.

In the past we had many problems with TEMP tablespace, when ever we do parallel index creation and analyze big tables and indexes. Those who had experience with "ANALYZE" command on a big table (100 million rows) on HP machines can understand what I am talking.

Do you use 8i or above? If so, why don't you create your TEMP tablespaces with tempfiles? If so, how do you coalesce then? Running

alter tablespace TEMP coalesce;

on a TEMP tablespace created with the tempfile keyword results in error.

[amits]

I also have the similiar problem with the Temp Tablespace.In my case i have set the Max Size 1gb ,so someone has given a analyze compute statistics command on a big table so the temporary datafile reaches its maximum size and has given some internal error now it is giving me the following error if i am trying to take the datafile or tablespace offline.The error code is as below:
ORA-00600: internal error code, arguments: [1113], [], [], [], [], [], [], []
Please suggest some thing how to resolve this problem.
Thanks
Amit

[julian]

Originally posted by amits
I also have the similiar problem with the Temp Tablespace.In my case i have set the Max Size 1gb ,so someone has given a analyze compute statistics command on a big table so the temporary datafile reaches its maximum size and has given some internal error now it is giving me the following error if i am trying to take the datafile or tablespace offline.The error code is as below:
ORA-00600: internal error code, arguments: [1113], [], [], [], [], [], [], []
Please suggest some thing how to resolve this problem.
Thanks
Amit

I have faced this problem once in a development DB. As far as I remember, I solved the problem by dropping the temp tablespace, bounced the instance, and then made a bigger temp tablespace. Then the 600 error dissappered. If you cannot drop the temp tablespace before shutdown, drop it straight after startup.