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$TEMPFILE V$TEMPSTAT V$TEMP_EXTENT_MAP V$TEMP_EXTENT_POOL V$TEMP_SPACE_HEADER V$TEMPSEG_USAGE (Oracle 9i and later releases) V$SORT_USAGE (Oracle 8.1.7, 8.1.6 and 8.1.5)
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 10.2.0.4 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,
file_id,
extents_cached extents_allocated,
extents_used,
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
SQL>
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
SQL>
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
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
SQL>
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
SQL>
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.