how can i empty the temp tablespace

[beksalur]

our temp tablespace is %100 full.what happens in this situation.is our server run slowly because of it?


how can i empty this tablespace to %0?

thanks in advance.

 

[Turkbear]

Hi,
If it is a tablespace of TEMPORARY type, then you do not need to 'empty' it..It has reusable space and will often show close to 100% full when checked on. It will revert to its initial size when Oracle is restarted.

 

[beksalur]

but i want to empty it without restarting the database.because i think when it is full server will be slow as sort_area_size is only 1 MB and for big sort it is not enough area.

 

[SantaMufasa]

Beksalur,

In Oracle 9i, if Oracle created your TEMP tablespace for you, the tablespace behaves slightly differently from earlier versions. (I, personally, use the pre-9i versions of TEMP tablespaces so that I can "see" and manage TEMP tablespaces in a manner consistent with my applications tablespaces.)

So, what I do to bring my TEMP tablespace to 0% is run a script with the following code (so that the TEMP tablespace is "gone" for only a couple of seconds. I usually run this code at during off hours to reduce the liklihood of someone using the TEMP tablespace during the code execution):

1) alter tablespace temp offline;
2) drop tablespace temp including contents;
3) create tablespace temp datafile '<fully qualified file name>'
size 10m reuse autoextend on next 10m maxsize 2000m;

The &quot;reuse&quot; option above allows you to specify the same name as the previous, larger file, but it reduces the size of the file to 10MB and frees you up from needing to &quot;rm&quot; the old, larger, obsolete file from your file system.

If your TEMP tablespace is a standard tablespace, you can use either of the following additional commands to obtain additional file space for TEMP:

1) ALTER TABLESPACE TEMP add datafile '<new file name'
size 10m autoextend on next 10m maxsize 2000m;
or
2) ALTER DATABASE DATAFILE '<name of existing TEMP data file>'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

In all options, above, I place a 2GB (2000MB) maxsize on the files to avoid operating-system issues with files greater than 2GB and to facilitate file management during backups and recoveries.

I hope you find this useful.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:32 (29Dec03) GMT, 09:32 (29Dec03) Mountain Time)

 

[sem]

So increase sort_area_size! It makes no difference whether your temporary tablespace is full or not.

Regards, Dima

http://seminihin.narod.ru

 

[carp]

Beksalur -
Wouldn't it make more sense to increase the size of your temp tablespace? And if it's NOT already a TEMPORARY tablespace, I would strongly encourage you to make it so at your earliest opportunity!

 

[sem]

If you experience performace issues, I suppose it may be due to the lack of appropriate indexes or, if they exist, of actual statistics. Huge amount of sorting is in most cases a sign of poor execution plans.

Regards, Dima

http://seminihin.narod.ru

 

[petemahaf]

you could try just resetting the storage parameters for the TEMP tablespace. This can be done at any time.

ALTER TABLESPACE temp DEFAULT STORAGE (pctincrease 0);

This will re-allocate the extents not in use and so clear the tablespace. It will not last as Oracle allocates and does not de-allocate these extents. You do not have to drop the tablespace and re-create it.