Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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:

TEMPFILE ?/ora01/oracle/oradata/booktst_temp_01.dbf? SIZE 50m;

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

   a.bytes c3,
   dba_temp_files a,
   (SELECT file_id, SUM(bytes) free_bytes
      FROM dba_free_space b GROUP BY file_id) b

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%'
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:

   see code depot for full scripts
   v$session    a,
   v$sort_usage srt
   a.saddr = srt.session_addr
order by
   srt.tablespace, srt.segfile#, srt.segblk#,


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:


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.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational