what is the best practice for sizing the temp tablespace?

what the best way to free up temp tablespace space?

how to monitor temp tablespace parameters?

Shawn wrote:

Why would you care about freeing up space in the temp tablespace?
Oracle manages it ... not the DBA.

That said:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 100M;

Funny ... probably haven't posted this piece of syntax in two years and
here it is twice in 10 minutes.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)

On 12 Sep 2004 22:08:46 -0700, farzan_toronto101 (AT) yahoo (DOT) com (Shawn)
wrote:

a) The theoretically correct but difficult solution :

Calculate the maximum temp space needed for all concurrent sorting
operations in your DB and size hereafter.

b) The practical solution :

Create a temp tablespace with a small initial size. Set autoextend on
and let it grow as necessary. Just assure that you have enough disk
space for it, e.g. :

Create temporary tablespace TEMP tempfile
'c:\temp_001.dbf' size 128m autoextend on next 128m maxsize 2048m,
'c:\temp_002.dbf' size 128m autoextend on next 128m maxsize 2048m
uniform size 16m;


This ensures 4 Gb of temp space but avoids wasting disk space.

If you follow the above, no need to. Unless your application's need
for temp tablespace is somehow reduced. Then you may try
alter database tempfile 'c:\temp_002.dbf' resize 64m;



Not sure what you mean. If you want to monitor how much of a temp
tablespace is actually used, it's

select a.tablespace_name,
round(Total_Space) as Current_space_Mb,
round(Max_Space) as Max_Space_Mb,
round(Used_Space) as Used_Space_Mb,
round(Used_Space/Total_Space*100,1) as Pct_Used_of_Current_Space,
round(Used_Space/Max_Space*100,1) as Pct_Used_of_Max_Space
from
(select tablespace_name,sum(bytes)/1024/1024 as Total_Space
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_used)/1024/1024 as Used_Space
from v$temp_extent_pool
group by tablespace_name) b,
(select
tablespace_name,sum(decode(maxbytes,0,bytes,maxbyt es)/1024/1024) as
Max_Space
from dba_temp_files
group by tablespace_name) c
where a.tablespace_name = b.tablespace_name
and b.tablespace_name = c.tablespace_name;

This applies to temp tablespaces created with tempfiles. For temp
tablespaces created with datafiles (which is obsolete but still seen),
its

select a.tablespace_name,
Total_Space as Current_space_Mb,
Max_Space as Max_Space_Mb,
round(Used_Space) as Used_Space_Mb,
round(Used_Space/Total_Space*100,1) as Pct_Used_of_Current_Space,
round(Used_Space/Max_Space*100,1) as Pct_Used_of_Max_Space
from
(select tablespace_name,sum(bytes)/1024/1024 as Total_Space
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as Used_Space
from dba_segments
group by tablespace_name) b,
(select
tablespace_name,sum(decode(maxbytes,0,bytes,maxbyt es)/1024/1024) as
Max_Space
from dba_data_files
group by tablespace_name ) c
where a.tablespace_name = b.tablespace_name
and b.tablespace_name = c.tablespace_name
and a.tablespace_name in (select tablespace_name from dba_tablespaces
where contents = 'TEMPORARY')


- Kenneth Koenraadt

On Wed, 09 Aug 2006 01:25:24 -0500, airmax wrote:

1) Don't panic. This may not be a problem at all!

2) Ask this (and other questions) in a newsgroup that is not obsolete ...
comp.databases.oracle.server is the appropriate group. I've copied to
that group and set the followup and replies to that one for your
convenience.

3) Tell us which version of the database and operating system you are
using. In this case it's probably not significant, but it leaves us (who
answer) with the impression that you care about the environment - making
us much more likely to answer civilly.

4) Read the documentation. IN particular, the following:
http://download-east.oracle.com/docs....htm#sthref399
http://download-east.oracle.com/docs....htm#sthref422
http://download-east.oracle.com/docs....htm#sthref543

What these links are telling you ... if the temp space is used for
sorting, it will stay allocated (show 'not free') but will automatically
be used for other sort operations when the current session is finished.

Since a block in a tablespace must be formatted before it is used, leaving
the blocks allocated for sorting (therefore appropriately formatted)
probably saves time. Clearing them might actually introduce a performance
hit. (Note to self - test this!)



So ... your problem is probably not
"how do I keep my temp tablespace from being full?",
but rather
"how much of my temp tablespace is allocated to reusable sort segments?"




By the way ... how big is the disk? Is 1 GB really a problem?


--
Hans Forbrich (mailto: Fuzzy.GreyBeard_at_gmail.com)
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.