Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted October 12, 2017

WEBINAR: On-demand webcast

How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >

Oracle's TEMP Tablespace

By David Fitzjarrell

Since version 8.1.5, Oracle has provided what can be considered as a true temporary tablespace, utilizing sparse files (or, in Oracle-speak, tempfiles). These files are essentially 'anchors', at the operating system level, consuming a file name and an inode (on *nix-type operating systems) and information on how large they can grow. By now this should be common knowledge but sometimes what may be thought of as 'common' may not be, thus the explanation. Having that out of the way let's go on and discuss how best to monitor and manage the 'true' temporary tablespace.

Temporary tablespace management begins with several V$ views:


Let's look at each and see what information it provides.

V$TEMP_EXTENT_MAP reports all of the allocated extents in the temporary tablespace:

SQL> desc v$temp_extent_map
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 OWNER                                              NUMBER
 RELATIVE_FNO                                       NUMBER

Notice there is no 'ownership' information here, just a catalog of the TEMPORARY extents allocated in each TEMPORARY tablespace. To see who 'owns' the segments one needs to query another view, V$TEMPSEG_USAGE (or V$SORT_USAGE, an older view that's been redefined to match V$TEMPSEG_USAGE since versions older than 9i use that name to manage sort (read temp) segments:

SQL> desc v$tempseg_usage
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                           VARCHAR2(30)
 USER                                               VARCHAR2(30)
 SESSION_ADDR                                       RAW(8)
 SESSION_NUM                                        NUMBER
 SQLADDR                                            RAW(8)
 SQLHASH                                            NUMBER
 SQL_ID                                             VARCHAR2(13)
 TABLESPACE                                         VARCHAR2(31)
 CONTENTS                                           VARCHAR2(9)
 SEGTYPE                                            VARCHAR2(9)
 SEGFILE#                                           NUMBER
 SEGBLK#                                            NUMBER
 EXTENTS                                            NUMBER
 BLOCKS                                             NUMBER
 SEGRFNO#                                           NUMBER

The V$TEMP_EXTENT_POOL view reports on the extent usage as aggregate values by tablespace:

SQL> desc v$temp_extent_pool
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
 FILE_ID                                            NUMBER
 EXTENTS_CACHED                                     NUMBER
 EXTENTS_USED                                       NUMBER
 BLOCKS_CACHED                                      NUMBER
 BLOCKS_USED                                        NUMBER
 BYTES_CACHED                                       NUMBER
 BYTES_USED                                         NUMBER
 RELATIVE_FNO                                       NUMBER

The simple task of querying V$TEMPSEG_USAGE or V$SORT_USAGE over time is all it should require to monitor the temporary tablespace usage:

-- 9i and later

select segtype, extents, blocks
from v$tempseg_usage
where tablespace  = ;

-- 8.1.5, 8.1.6, 8.1.7

select segtype, extents, blocks
from v$sort_usage
where tablespace  = ;

No rows returned means no attention is needed by the DBA; that may be confusing as such a result can fly in the face of the results from V$TEMP_EXTENT_POOL but remember that V$TEMP_EXTENT_POOL reports only allocated extents and simply allocating extents in a temporary tablespace does not indicate a space problem exists. Even if the above query returns rows (and in an active database it most likely will) unless you are seeing allocation errors you need not worry about space.

The query below will produce a fairly useful allocation/usage report:

select tablespace_name,
       extents_cached extents_allocated,
       bytes_cached/1024/1024 mb_allocated,
       bytes_used/1024/1024 mb_used
from v$temp_extent_pool

Want a more detailed report? The following query returns the user name, the 'offending' query, the extents and blocks of temporary space consumed:

select u.username, s.sql_fulltext, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;

If you're still using 9iR2 and/or 9iR1 that use true temporary tablespaces (and if you are, that begs the question 'Why?') the following modified query returns that same information:

select u.username, s.sql_text, u.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.address = u.sqladdr
and s.hash_value = u.sqlhash;

V$TEMP_SPACE_HEADER provides a less-detailed view of the available space; it lists allocated and non-allocated space in the tempfiles, by file. It's important to remember that in a true temporary tablespace allocated extents are not necessarily used extents; a temporary tablespace that is 100% allocated is NOT a problem since extents are re-used by sessions as they are needed.

A commonly asked question is "How much space do I need in my temporary tablespace?" That, of course, would depend on how 'active' your system is, how many concurrent and active sessions there are, the sizes of those transactions and how much disk space you have available. It's not unusual to increase the temporary tablespace size over time as the number of active sessions can increase, the size of the transactions can grow larger and query durations can get longer. Seeing ORA-01652 errors (unable to extend temp segment by 128 in tablespace TEMP, for example) will alert you to the need for more space; those can also alert you to the need to tune queries that are using considerable amounts of temporary space as the plans may not be optimal for the volume of data returned. The number reported by an ORA-01652 error is in blocks; converting that value is easy using the db_block_size value so you know how many bytes the temporary tablespace is 'short'. Since there is no 'rule of thumb' to size a temporary tablespace each situation requires individual attention to provide sufficient space for the particular database being managed.

The current size of your temporary tablespace can be found by querying the DBA_TEMP_FILES view:

SQL> select tablespace_name, sum(bytes)/1024/1024 MB
  2  from dba_temp_files
  3  group by tablespace_name
  4  /

TABLESPACE_NAME                        MB
------------------------------ ----------
TEMP                                 1024


If, because of file corruption, you need to drop and recreate your TEMP tablespace you cannot perform that act when there are connected users so you'll need to do the following:

Create a new temporary tablespace, make that the database default, then drop the original temporary tablespace.

If you're attached to the old temporary tablespace name you can recreate it (since the default temporary tablespace has another name) and go through the process shown above to make IT the default temporary tablespace. Obviously, you'll then need to drop the 'interim' temporary tablespace.

When tuning queries the V$SQL_PLAN view can provide information on estimated temporary space usage provided you have the SQL_ID of the query:

select temp_space from v$sql_plan where sql_id = '';

Temporary space usage can involve any number of areas for you to monitor: execution plans generated by DBMS_XPLAN.DISPLAY_CURSOR() are usually the best place to start as they can provide statistics that EXPLAIN PLAN and tkprof can't (like estimated temporary space usage):

Plan hash value: 1887592732

| Id  | Operation                | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT         |                            |       |       |       |  2745 (100)|          |
|*  1 |  HASH JOIN ANTI          |                            |   237 |   469K|       |  2745   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL      | LF_HOTEL_TEMP              |   237 |   466K|       |   176   (4)| 00:00:01 |
|   3 |   VIEW                   |                            |   478K|  5607K|       |  2565   (4)| 00:00:01 |
|*  4 |    FILTER                |                            |       |       |       |            |          |
|   5 |     HASH GROUP BY        |                            |   478K|  5607K|       |  2565   (4)| 00:00:01 |
|   6 |      VIEW                |                            |   478K|  5607K|       |  2526   (2)| 00:00:01 |
|   7 |       UNION-ALL          |                            |       |       |       |            |          |
|   8 |        HASH UNIQUE       |                            |   215K|  2525K|  4240K|  1136   (2)| 00:00:01 |
|*  9 |         TABLE ACCESS FULL| LF_TS_ROOMTYPE_PROPERTIES  |   215K|  2525K|       |   140   (3)| 00:00:01 |
|  10 |        HASH UNIQUE       |                            |   125K|  1467K|  2464K|   683   (2)| 00:00:01 |
|* 11 |         TABLE ACCESS FULL| LF_GTA_ROOMTYPE_PROPERTIES |   125K|  1467K|       |   104   (2)| 00:00:01 |
|  12 |        HASH UNIQUE       |                            |   137K|  1614K|  2712K|   707   (2)| 00:00:01 |
|* 13 |         TABLE ACCESS FULL| LF_HB_ROOMTYPE_PROPERTIES  |   137K|  1614K|       |    70   (3)| 00:00:01 |

Predicate Information (identified by operation id):

   1 - access("A"."HOTEL_CODE"="B"."HOTEL_CODE")
   2 - filter("A"."SERVICE_ID"=:P_SERVICE_ID)
   4 - filter(COUNT(*)=3)
   9 - filter("HOTEL_CODE" IS NOT NULL)
  11 - filter("HOTEL_CODE" IS NOT NULL)
  13 - filter("HOTEL_CODE" IS NOT NULL)

   - dynamic statistics used: dynamic sampling (level=2)

Armed with this information you can investigate why that amount of temp space is required and possibly reduce temp space usage by, say, adding an index to reduce the overall volume of data processed. Not all temporary space management involves modifying the temporary tablespace.

Hopefully this prepares you for examining, and possibly attacking, temporary tablespace problems when they exist and gives you the insight to know when a perceived 'issue' is not really an issue at all.

See all articles by David Fitzjarrell

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM