 |
|
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:
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, 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. |
|