Temporary Tablespaces Oracle Tips by Burleson Consulting

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 like it’s 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:

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, don’t 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;

Tablespace Data Dictionary Views

Oracle provides data dictionary views for tablespaces in your database. They are:

View Name            Description
dba_tablespaces       Describes each tablespace
dba_data_files          Lists each datafile in the database.
dba_temp_files         Describes each tempfile in the database

Let’s see how these data dictionary files fit together.  Here is a dictionary query to show the mapping between the logical tablespaces and the physical data files:

We can use the data dictionary to give us all-sorts of other internal details, such as this script to display the available space within each tablespace:

Conclusion

It is finally time to create objects to store in our database.  These objects can include tables, indexes, and many other types of segments.  In this chapter we have defined the base foundation that your database must have.  Adding tables, inserting rows, and so on cannot occur without the different structures we have defined here.

Just think of it this way: your house is finally ready to customize!  Your foundation is in place, the boards are up, the drywall is finally in place.  Now it is time for you to put a few things in it and really put it to use.

The main points of this chapter include:

* Oracle allows you to create logical tablespaces which are a logical collection of one-or-more physical disk datafiles.

* Oracle has Automatic Memory Management (AMM) to hide the complexity of the logical-to-physical file mapping.

* Oracle has “temporary” tablespace that are used to sort large results from SQL statements.

This is an excerpt from the bestselling "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle Certified Master).  It’s only $19.95 when you buy it directly from the publisher here.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.