RelationalDBDesign RelationalDBDesign


Creating Users   «Prev  Next»
Lesson 5Temporary Tablespaces
Objective Choose a Temporary Tablespace for a User

Choose a Temporary Tablespace for a User

Temporary tablespaces are used for operations like a large sort that requires disk space on a short-term basis. Assigning a user to a temporary tablespace allows you to segregate this type of I/O activity, and place it on a set of disks that are not being used for other purposes. If you do not assign a user to a temporary tablespace, then sorts and similar operations will use the SYSTEM tablespace. That can have a disastrous impact on performance, because the SYSTEM tablespace contains the data dictionary objects and already is heavily used. Compare the two situations shown in the following MouseOver.

  1. MARYBETH is experiencing plenty of I/O throughput to and from the TEMP tablespace. Her sort is progressing rapidly
  2. JOSEPH is experiencing limited throughput because his large sort is using the SYSTEM tablespace, which other users also are using heavily.
  3. A storm is brewing over the DBA who can expect to receive some phone calls from irate users.
  4. JOSEPH's sort is hurting everyone's performance. Where is that DBA, anyway?
  5. JOSEPH's sort is hurting everyone's performance. Where is that DBA, anyway?

Better Performance
When the user named MARYBETH issues a query requiring a sort, no other database users are affected unless they too are using the TEMP tablespace. On the other hand, when JOSEPH requires a sort, it places a heavy I/O burden on the SYSTEM tablespace, and could have a drastic effect on the performance experienced by all database users.
Most databases are constructed with at least one or more temporary tablespaces. In the case of the COIN database, the temporary tablespace is named TEMP.

Temporary Tablespaces

Temporary tablespaces are used for special operations, particularly for sorting data results on disk and for hash joins in SQL.
For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk. The temporary tablespace is where this takes place.
Each database should have one temporary tablespace that is created when the database is created. You create, drop and manage tablespaces with
  1. create temporary tablespace,
  2. drop temporary tablespace and
  3. alter temporary tablespace commands,
each of which is similar to its create tablespace counterpart.
The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles.
Thus, instead of using the datafiles keyword you use the tempfiles keyword when issuing a create, drop or alter tablespace command as you can see in these examples:

CREATE TEMPORARY TABLESPACE temp
TEMPFILE ?/ora01/oracle/oradata/booktst_temp_01.dbf? SIZE 50m;
DROP TEMPORARY TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tempfiles are a bit different than datafiles in that you may not immediately see them grow to the size that they have been allocated (this particular functionality is platform dependent). Hence, do not panic if you see a file that looks too small.

col allocated_bytes format 999,999,999,999,999
col free_bytes format 999,999,999,999,999

select  
   a.tablespace_name,  
   a.file_name,  
   a.bytes c3,  
   b.free_bytes  
FROM 
   dba_temp_files a, 
   (SELECT file_id, SUM(bytes) free_bytes
      FROM dba_free_space b GROUP BY file_id) b
WHERE  
a.file_id=b.file_id
ORDER BY 
 a.tablespace_name;

Here is a script that will display the contents of the TEMP tablespace.
set pagesize 60 linesize 132 verify off  
break on file_id skip 1 
 
column file_id heading "File|Id"  
column tablespace_name for a15  
column object          for a15 
column owner           for a15 
column MBytes          for 999,999 

select tablespace_name, 
'free space' owner, /*"owner" of free space */  
' ' object,         /*blank object name */ 
file_id, /*file id for the extent header*/ 
block_id, /*block id for the extent header*/  
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_free_space 
where tablespace_name like '%TEMP%' 
 union 
select tablespace_name, 
substr(owner, 1, 20), /*owner name (first 20 chars)*/ 
 substr(segment_name, 1, 32), /*segment name */ 
file_id, /*file id for extent header */  
block_id, /*block id for extent header */ 
CEIL(blocks*4/1024) MBytes /*length of the extent, in Mega Bytes*/ 
from dba_extents 
where tablespace_name like '%TEMP%'  
order by 1, 4, 5  
/ 

You can check for held TEMP segments with this query:
select  
   srt.tablespace,  
   srt.segfile#,  
   srt.segblk#,  
   srt.blocks,  
   a.sid,  
   a.serial#,  
   a.username,  
   a.osuser,  
   a.status  
from 
    see code depot for full scripts 
   v$session    a,
   v$sort_usage srt  
where  
   a.saddr = srt.session_addr  
order by  
   srt.tablespace, srt.segfile#, srt.segblk#,  
   srt.blocks;