Oracle Tips by Burleson Consulting
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
TEMPORARY TABLESPACE temp
‘/ora01/oracle/oradata/booktst_temp_01.dbf’ SIZE 50m;
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, don’t panic if you
see a file that looks too small.
col allocated_bytes format
col free_bytes format 999,999,999,999,999
(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
set pagesize 60 linesize 132 verify off
break on file_id skip 1
column file_id heading "File|Id"
column tablespace_name for a15
'free space' owner, /*"owner" of free space */
' ' object,
/*blank object name */
/*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*/
tablespace_name like '%TEMP%'
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
/*length of the extent, in Mega Bytes*/
where tablespace_name like '%TEMP%'
order by 1, 4, 5
You can check for held
TEMP segments with this query:
see code depot for
a.saddr = srt.session_addr
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
Lists each datafile in the database.
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
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.