Monitoring Oracle’s Temp Usage

It appears that Oracle’s temporary tablespace is still a mystery to some, possibly because it’s not managed in the same way as a conventional tablespace. Extents are allocated and re-used so space management looks nothing like that for a traditional tablespace. Because of that space errors are managed differently. Let’s look at how to manage a temporary tablespace and what tools are at the DBA’s disposal.

Starting with version 8.1.5 Oracle has provided a ‘true’ temporary tablespace where extents are allocated and assigned to sessions as they are needed. The extents are never dropped while the database is running so a properly utilized temporary tablespace will always have 0 ‘free’ space. In this sense ‘free’ means unallocated, and everything in the temporary tablespace will be allocated but that doesn’t mean it’s being used. This is why conventional space management has no place in the temporary tablespace.

So how IS the temporary tablespace managed? It’s done through several V$ views:


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

The most helpful views to manage temporary tablespaces are V$TEMP_EXTENT_POOL and V$TEMPSEG_USAGE/V$SORT_USAGE. V$TEMP_EXTENT_POOL lists not only the allocated extents, it also reports on all used extents in a temporary tablespace by tempfile:

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

V$TEMPSEG_USAGE (or, if you prefer, V$SORT_USAGE) shows the temporary segment usage, by user, for all tablespaces:

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 

[In and later releases V$SORT_USAGE and V$TEMPSEG_USAGE have the same definition, with V$SORT_USAGE provided simply for backward compatibility.]

Temporary tablespace usage monitoring is a simple task of querying V$TEMPSEG_USAGE (V$SORT_USAGE, if that is preferred) over time:

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

Remember that such monitoring will produce no actionable data; unless the database is reporting errors allocating temp space the DBA really has nothing to do.

If you’d like to know how many extents are allocated and actually used per datafile the following query will produce a fairly useful 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

Again, no action needs to be taken based on such a report; it’s merely for informational purposes.

Who’s using your temp space, what queries are they executing and how much of that space is each one consuming? That’s also a fairly easy task to complete:

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;

The V$TEMP_SPACE_HEADER view provides a less granular view of the consumed and available space; it lists the allocated and non-allocated space in the tempfiles, by file. Keep in mind that allocated does not mean used; there will likely be large differences between what V$TEMP_SPACE_HEADER reports and what V$TEMPSEG_USAGE provides.

I will quote myself (from somewhere else on the web) with regard to proper sizing of temporary tablespaces: “So how much space do you need in your temporary tablespace? That would depend upon how active your system is, how many concurrent active sessions there are, the size of the transactions and how much disk space you have. It isn’t a disgrace to increase your TEMP tablespace size over time as usage patterns, number of users and data volumes change. Oracle will also inform you that the temporary tablespace needs to be increased by issuing ORA-01652 errors (unable to extend temp segment by 128 in tablespace TEMP, for example). [ORA-01652 errors can occur for ANY tablespace in the database, and the affected resource will be listed in the error text: “ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM” indicates that the SYSTEM tablespace is needing to be increased. Again, ANY tablespace in the database can be listed in an ORA-01652, not just the TEMP tablespace. And the transaction which threw the error was rolled back because of it, thereby freeing the space it had already consumed when the error condition was encountered. An interesting side note: simply because an ORA-01652 error is displayed for a non-temporary tablespace doesn’t mean that there are temporary objects created there as when tables/indexes are created or extended the extents allocated are listed as temporary until the DDL completes at which time the created extents are given their ‘permanent’ object name. When regular tablespaces throw this error then there is insufficient space to extend tables and/or indexes and inserts/updates will fail. So, when an ORA-01652 error appears look carefully at the tablespace listed in the error text as it may not be a temporary tablespace issue.] The number reported in an ORA-01652 error is in blocks, not bytes, so you’ll need to convert that using the db_block_size value to know how many bytes the temporary tablespace needed to complete the transaction generating the error. There is no ‘rule of thumb’ to size a temporary tablespace because such rules usually create situations where the only tool becomes a hammer and every task ends up as a nail, and more often than not, you hit that thumb with the only ‘tool’ you’ve been given.”

DBA_TEMP_FILES will tell you how large the temporary tablespace is:

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


It can also report which files are associated with your temporary tablespace:

SQL> select tablespace_name, file_name, bytes
  2  from dba_temp_files
  3  order by tablespace_name, file_name
  4  /

TABLESPACE_NAME FILE_NAME                                                    BYTES
--------------- ------------------------------------------------------- ----------
TEMP            /u2/orawiz/parlopnett/temp01.dbf                        1073741824


If you want, or need, to decrease the size of your TEMP tablespace you need to shut the database down, open it in restricted mode, resize the tempfile (or tempfiles) smaller then shutdown and startup the database. Adding space isn’t nearly as involved, as a simple ‘alter database tempfile resize ;’ increases the space.

What happens if the temporary tablespace doesn’t exist or isn’t available? Oracle will display this:

SQL> select * from mybigtable order by mycoolkey;
select * from mybigtable order by mycoolkey
ERROR at line 1:
ORA-01129: user's default or temporary tablespace does not exist 


If you see this error message check to see if the temporary tablespace exists and is online. Correct any issues you find (create the missing tablespace or put the tempfiles back online).

If the tablespace exists but no tempfiles are assigned to it (which can occur after a database restore/recover from a backup) then Oracle reports:

SQL> select * from mybigtable order by mycoolkey;
select * from mybigtable order by mycoolkey
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty 


So, the task is simply to issue ‘alter temporary tablespace … add tempfile …’ commands to make the tablespace no longer empty.

Managing the temporary tablespace isn’t rocket science, but it does differ (sometimes considerably) from the ‘normal’ tablespace management procedures employed for regular data files. Knowing that a ‘full’ temporary tablespace is not an issue (remember that segments are allocated then reused) can make the task a bit easier as Oracle will tell you (with ORA-01652 errors) if you’ve run out of space. Which means the DBA can spend time on useful tasks, like password resets.

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

Latest Articles