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.
MARYBETH is experiencing plenty of I/O throughput to and from the TEMP tablespace. Her sort is progressing rapidly
JOSEPH is experiencing limited throughput because his large sort is using the SYSTEM tablespace, which other users also are using heavily.
A storm is brewing over the DBA who can expect to receive some phone calls from irate users.
JOSEPH's sort is hurting everyone's performance. Where is that DBA, anyway?
JOSEPH's sort is hurting everyone's performance. Where is that DBA, anyway?
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
create temporary tablespace,
drop temporary tablespace and
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;