Oracle’s TEMP Tablespace

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

David Fitzjarrell
David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.
Get the Free Newsletter!
Subscribe to Cloud Insider for top news, trends & analysis
This email address is invalid.

Latest Articles