Re: temp tablespace

From: <Kenneth>
Date: Mon, 13 Sep 2004 16:21:32 GMT
Message-ID: <4145c3fb.1791875@news.inet.tele.dk>

On 12 Sep 2004 22:08:46 -0700, farzan_toronto101_at_yahoo.com (Shawn) wrote:

>what is the best practice for sizing the temp tablespace?

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

>what the best way to free up temp tablespace 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;

>
>how to monitor temp tablespace parameters?

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,maxbytes)/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,maxbytes)/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

Received on Mon Sep 13 2004 - 11:21:32 CDT