TEMP tablespace grows all the time!

[SGA]

Hello All.

RDBMS: 8.1.7.3
O/S : Sun Solaris 8


Localy Managed Temporary tablespace (with unlimited autoextend enabled
on datafiles) is 40G already - and keeps growing.
Tablespace is locally managed, and the DB is 24x7 environment,
so shutdown is not an option to reduce the tablespace size.

I know such a problem exists since early 8i versions (at least 8.1.6 and up),
but nobody seems to know the answer ...

Please Advice.
Regards.

[julian]

It is unclear from your message if you created it with the TEMPFILE keyword. However, what makes you bother about how big it is?

[SGA]

Thanks for you reply.

I used tempfile keyword of course , and the uniform extent
(SORT_AREA_SIZE + DB_BLOCK_SIZE) allocation exists.

I am concerned of the size because I dont want it to be too large, not more than 10G. The way it grows today it will get to
100G in a couple of weeks.

I am pretty sure there is a way to clear it without bouncing the DB...

Regards.

[Raminder]

I think there is no other way but to restart the db. Or drop and recreate the temp tablespace making sure that no active connections are using the temp tablespace during that time.

[ovidius]

Create a new temp tablespace, switch it (create temporary tablespace "new_temp_tablespace" tempfile '.../...' size XXXXM extent management local uniform size 100M bla-bla, then: alter database default temporary tablespace "the_new_created_tablespace"), wait untill it becomes inactive (all thw transactions using it are finished), then drop it.

(Then delete the file and repeat the procedure "reversing" names if you want it where it initially was.)

THIS WORKS FINE WITH 9i. BECAUSE I DO NOT KNOW IF IT WORKS WITH 8i, MAKE A TEST BEFORE...


[Edited by ovidius on 04-01-2002 at 04:39 AM]

[jgmagnus]

i assume it is fragmented if it is a temp tablespace.
check to see if it's fragmented and then coalesce it. see if that lowers the space utilization.

certainly will not solve you problem per se ...

- Magnus

[julian]

Originally posted by SGA
Thanks for you reply.

I used tempfile keyword of course , and the uniform extent
(SORT_AREA_SIZE + DB_BLOCK_SIZE) allocation exists.

I am concerned of the size because I dont want it to be too large, not more than 10G. The way it grows today it will get to
100G in a couple of weeks.

I am pretty sure there is a way to clear it without bouncing the DB...

Regards.

I have had a similar problem before (with 8.1.7). What jgmagnus suggested will not work. You cannot run

ALTER TABLESPACE TEMP COALESCE;

Well, you can of course but you will get ORA-3217.

Bounce the base!

[tamilselvan]

Analyze table requires more space in the temp tablespace.

Do not analyze a big table ( > 100 million rows) with 100 %.

Analyze only 5 to 10 % on big tables.


When there is low level of transactions, try to coalesce the temp tablespace.

[jgmagnus]

Originally posted by tamilselvan
When there is low level of transactions, try to coalesce the temp tablespace.

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 ...

- magnus

[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 ...

It depends on what kind of "temporary" your temporary tablespace realy is. If you are using tempfiles (as ovidius suggested) then you can't coalesce such temp tablespace (as julian pointed out).

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.