TEMPORARY (Tablespace) Insanity?

Some poor soul has the misguided notion that if a true temporary tablespace is 100% allocated it needs attention, that 'attention' being dropping and recreating the temporary tablespace. (He's also posted a number of other 'items' regarding Oracle databases which are questionable, if not incorrect.) In the absence of error messages this concept could not be further from the truth. Let's look at the temporary tablespace mechanism in Oracle 9i and later releases and see why such a recommendation is, well, wrong.

Oracle, since version 8.1.5, has provided a true temporary tablespace, utilizing sparse files (or, as Oracle has you declare them, tempfiles). [8i is the last release where a non-sparse file temporary tablespace can be created; in 9i and later releases attempting to create a temporary tablespace using datafiles will produce an error.] These files are, at the operating system level, markers for the maximum size these sparse files can attain. To prove this simply allocate a new tempfile to an existing temp tablespace and see what happens. The allocation takes almost no time and the file system usage does not increase, indicating that, on a UNIX/Linux system, the inode has been allocated and a marker has been set to restrict the size of the file to that specified in the alter tablespace command. [The df command incorrectly shows this file as the requested size; using du instead reveals the actual size of this tempfile (on Solaris, at least).] This file will not begin to increase in size until Oracle needs the additional temp space for transaction or query processing, at which time Oracle will allocate what it needs, then stop. Unless, of course, the underlying file system fills up before the allocation Oracle requested is fulfilled. Or if Oracle needs more space than you 'allocated' in the tempfile addition. This temporary tablespace configuration, which is locally managed, changes the allocation/usage mechanism DBAs were used to in prior versions of the database. Simply because segments are allocated in the temporary tablespace does not create a problem since extents, once allocated, can be reused by other processes and sessions, which prevents the tablespace from growing without bound allocating extents only a specific session can use. [I do not personally recommend using AUTOEXTEND on any tablespace, period. And that's a topic for another blog entry.]

Management of the temporary tablespace is afforded the DBA through several V$ views:

V$TEMPFILE
V$TEMPSTAT
V$TEMP_EXTENT_MAP
V$TEMP_EXTENT_POOL
V$TEMP_SPACE_HEADER
V$TEMPSEG_USAGE

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

Note this view doesn't report if they're used or not, simply that they are allocated. And allocated extents, in a true temporary tablespace, are not a problem. [For the curious among you the RELATIVE_FNO column provides the relative file number, which uniquely identifies a file within a tablespace. Normally this doesn't differ from the value in the FILE_ID column (which uniquely identifies a file in a database) unless there are more than 1023 datafiles in a database or if the tablespace is a bigfile tablespace, where the RELATIVE_FNO = 1024 (4096 if the database is running on the OS/390 platform).]

The V$TEMP_EXTENT_POOL and V$TEMPSEG_USAGE views are likely the most helpful for the ongoing management of a temporary tablespace. V$TEMP_EXTENT_POOL lists the allocated, and 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 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 

Monitoring the temporary tablespace is thus a simple task of querying V$TEMPSEG_USAGE over time:

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

Presuming no rows are returned your temp space needs no attention. Even if rows are returned it's likely no effort on the DBA's part is necessary, regardless of what V$TEMP_EXTENT_MAP reports.

Who's using your temp space, what queries are the executing and how much of that space is each one consuming? That's also a fairly easy task to complete. In 10g and later releases the following query returns the user, the query, the extents and blocks of temporary space consumed:

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

For 9iR2 and earlier releases that use true temporary tablespaces the following modified query returns the information:

select u.username, s.sql_text, 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 'rougher' view of the consumed and available space, although it lists the allocated and non-allocated space in the tempfiles, by file. And, simply because it's allocated doesn't mean it's being used, and you'll likely find a vast difference between what V$TEMP_SPACE_HEADER reports and what V$TEMPSEG_USAGE displays. I'd prefer to query V$TEMPSEG_USAGE as it reports the space which is actually being used versus that which has been used at some time in the past and remains allocated. Oh, and you can't 'clean' the V$TEMP_SPACE_HEADER view by doing anything except restarting the database or dropping and recreating the TEMP tablespace. I don't know why you'd want to do that in the first place, but, hey, people ask some interesting questions.

Being that true temporary tablespaces utilize sparse files allocating all of the extents is a very good idea, to allow that sparse file (or files) to consume all of the intended space on the file system. Such actions prevent surprises later, where the temp file won't fully allocate on a file system due to a lack of available space. The following query should allocate almost all of your temporary file storage:

select a.*
from dba_objects a, dba_objects b, dba_objects c
order by 1;

The cartesian join creates a huge data set and the ORDER BY ensures the temporary tablespace will extend to its allocated maximum by continually enlarging the sort segment until it simply cannot allocate the next required extent. And, as I said before, simply allocating 100% of your temp tablespace isn't a cause for concern; it's when you have errors because you've allocated all of your space and need more that the DBA needs to take action. And that action isn't to drop and recreate the temporary tablespace, it's to add the necessary space to keep transactions flowing either by resizing the current sparse file:

ALTER DATABASE TEMPFILE 'temp01.dbf' RESIZE 2048M;

or by adding another sparse file to the mix:

ALTER TABLESPACE TEMP ADD TEMPFILE 'temp02.dbf' SIZE 1024M;

Dropping and recreating the temporary tablespace is rarely necessary, and it's NOT to be done whenever the extent allocation reaches 100%, because, as mentioned previously, extents in such a tablespace can be, and will be, reused. Monitoring V$TEMPSEG_USAGE over a period of time for an active system will prove this, time and again.

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 volume change. Oracle will also inform you that the temporary tablespace needs to be increased by issuing ORA-01642 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.] 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.

How do you know the current size of your temporary tablespace? Oracle can provide that answer 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

SQL>

DBA_TEMP_FILES 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>

This can help in understanding how much space is allocated to your temporary tablespace and where those files are located.

Should you decide that you 'need' to reduce your TEMP tablespace size the you can do that with the ALTER DATABASE command:

ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

You can enlarge the file size at any time, but to reduce it it's best to shutdown the database, open in restricted mode then resize the temporary file to the smaller size, then shutdown and startup the database in normal mode.

How large can you make your temporary tablespace? As large as you want, within the limits of the available disk space. Temporary tablespaces can have multiple files, and those files can be as large as the filesystem allows (some antiquated filesystems restrict the size of a single file to 2 GB). So you CAN fill up all of the available disk space with your temp files, but that's most likely just wasting space since you don't normally need a TEMP tablespace in the terabytes. Also there is a 1023 file limit for each tablespace; trust me, I've never seen a TEMP tablespace that needed, or contained, 1023 temp files.

If, for some valid reason (such as file corruption), you actually need to drop and recreate your TEMP tablespace you cannot perform such a task with connected users so you'll need to either shutdown the database and open it in restricted mode to drop and recreate your TEMP tablespace or, if you cannot shutdown the database, create a new TEMPORARY tablespace with a slightly different name, then re-assign the users to this new TEMPORARY tablespace; at the next scheduled shutdown you would then drop the old TEMPORARY tablespace. As I said before use this method only if you have a valid reason for replacing your existing TEMPORARY tablespace, and 100% extent allocation is NOT a valid reason.

Yes, you can have more than one TEMPORARY tablespace in a database, and you can, in 10g, create tablespace groups of two or more TEMPORARY tablespaces and assign that group as a user's temporary tablespace. Tablespace groups are created when specified in the CREATE TEMPORARY TABLESPACE or in the ALTER TABLESPACE commands, as illuatrated:

-- Create second temporary tablespace and
-- assign it to group 1

CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oracle/data/temp201.dbf'
     SIZE 50M
     TABLESPACE GROUP group1;

-- Assign existing temporary tablespace temp1 to group 1

ALTER TABLESPACE temp1 TABLESPACE GROUP group1;

Now you have a tablespace group, group1, with two temporary tablespaces in it, temp1 and temp2, and they'll both be used for sorting and temporary object creation. Assigning the group to a user is also easy:

ALTER USER blorpo TEMPORARY TABLESPACE group1;

(You can even make a tablespace group the default temporary tablespace for a database:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE group1;

which is a useful option.) So now the user has two temporary tablespaces available which can help alleviate problems where sorts consume large amounts of disk and a single temporary tablespace doesn't provide the required space. Don't think, though, that you cannot generate an ORA-01652 error by using a tablespace group as a temporary tablespace, as those errors can, and will, happen under the right conditions. Remember that temporary tablespaces are shared resources, and the sort segment in each is also a shared resource which can be stretched to the limit the files will allow. Once that happens the ORA-01652 rears its head and you may be left wondering why. Querying the temp segment usage (and the sort segment usage) can show who is using the space and how much they are consuming. So, if you do receive an ORA-01652 error you have a two ways to fix the problem: add another temp tablespace to the group, or add space to the existing tablespaces in that group either by extending the existing tempfiles or by adding new tempfiles (my choice would be to add space to the existing tablespaces). Of course you can expand this group by adding another temporary tablespace to it (if, for example, you have space limitations on a disk group and need to use another stripe and would prefer to make that a new tablespace), and the users assigned this group will immediately reap the benefits of the additional resources.

The temporary tablespace group is dropped, by default, when all members of that group are dropped or removed from it. The following command removes tablespace temp1 from the tablespace group to which it once belonged:

ALTER TABLESPACE temp1 TABLESPACE GROUP '';

You can continue to assign temporary tablespaces to a null group; if a tablespace didn't belong to a group nothing changes, and if it did that group is reduced by 1 tablespace. If this is the last tablespace remaining in group1 then after this command completes that group would no longer exist. As such there is no 'drop tablespace group' command.

Knowing the overall size of your tablespace group is also fairly easy by using the DBA_TABLESPACE_GROUPS and DBA_TEMP_FILES views. A sample query is shown below; replace 'GROUP1' with the name of your tablespace group:

select sum(bytes)
from dba_temp_files
where tablespace_name in (select tablespace_name from dba_tablespace_groups where group_name = 'GROUP1');

Presuming your SYSTEM tablespace is locally managed it's required that the database have a default temporary tablespace as a locally managed SYSTEM tablespace cannot be used for default temporary storage. Thus, any user not assigned a specific TEMPORARY tablespace gets the database default temporary tablespace. If, perchance, the database has (gasp) a dictionary-managed SYSTEM tablespace then any user not assigned a temporary tablespace will use SYSTEM for temporary object storage (which is not the best idea). Also, a message will be written to the alert log saying such.

Can a user be assigned a regular tablespace as a temporary tablespace? In 8i and earlier releases, yes, but in 9i and later releases you'll get the following error:

SQL> create tablespace sortatemp
  2  datafile '/u02/oradata/mydb/sortatemp01.dbf' size 100M reuse;

Tablespace created.

SQL> alter user borg temporary tablespace sortatemp;
alter user borg temporary tablespace sortatemp
*
ERROR at line 1:
ORA-10615: Invalid tablespace type for temporary tablespace


SQL>

so for those of you thinking you'd simply love to convert your temporary tablespace to a regular, old tablespace think again, because you can't, as no one will be able to use it as you intended.

If you're using raw volumes (devices) you'll need to allocate a new raw partition to extend your TEMP tablespace, which involves the UNIX or Windows System Administrator; you cannot simply execute an 'alter tablespace TEMP add tempfile ...' command as that will create a 'cooked' sparse file which will not be what you want. Make the request known to your System Administrator and also include the size of the partition you desire. When this person has the new raw device created you'll be able to add it to the TEMP tablespace and increase the available storage.

So what's in the temporary tablespace? Transient data which 'disappears' when the session ends like sort segment extents, temporary LOB data, results of various hash operations and the rows in local temporary and global temporary tables. [Query execution plans can, in later releases of Oracle, provide an estimate on how much temp space a query can consume, so using 'explain plan' and querying the dbms_xplan.display 'table' can reveal such information.] Nothing anyone would need to keep around for any length of time. (Sort activity can possibly be reduced by increasing the sort_area_size and hash_area_size parameters so that a greater percentage of the sorts are done in memory.) If the global temporary tables are consuming large portions of your temporary tablespace the best course of action is to increase the size of that tablespace. Again, V$TEMPSEG_USAGE is the view to use, as it will report the blocks consumed. Knowing that value makes it easier to adjust the temporary tablespace size.

Reducing the temporary tablespace usage is not a simple question to answer as it can involve any number of adjustments to the database and the objects contained therein. As mentioned earlier increasing the sort_area_size and hash_area_size parameters can reduce some of the temporary tablespace usage by performing more of the larger sorts and hash joins in memory; tuning queries can also lead to smaller footprints in the temporary tablespace landscape as less data can be returned and thus less filter activity occurs to return the desired results. Some queries cannot be helped by indexing (such as those using subquery factoring [the WITH clause]) but others may. One type of query, involving longitude and latitude where both columns are indexed independently, would benefit from a concatenated index for longitude and latitude, as Oracle will most often choose the latitude index and then filter on longitude, using the temporary tablespace as a dumping ground. Including both in a single index converts the index access/filter operation to a simple index access task, reducing the consumption of temporary tablespace resources. How you reduce your temporary tablespace usage is determined by the types of queries involved, the tables and indexes used and how your memory parameters are configured. No one solution works for everyone, and not all queries can be 'fixed' to use less TEMP space. You can use the tools provided here to determine how much TEMP space you're using and how much of that space you could save by tuning your database in a responsible manner.

It seems that this post has gone a bit astray of the original topic of recreating a temporary tablespace when it's 100% allocated, but the information presented here has been requested by those using google.com in reference to temporary tablespace usage and management. I've tried to answer as broad of a range of questions as I can, but I'm sure I've missed something someone, somewhere, wants to know. I monitor the questions that create hits to this page, and when I see something I haven't presented I'll do my best to add that to this ever-growing list of temporary tablespace information. And, as we approach the end of this post, remember: if anyone tells you that if your temporary tablespace is 100% allocated you need to drop and recreate it, think again. Unless you've reached the end of the available space for a file system (and you've set your temporary tablespace to autoextend, which isn't a good idea) you don't need to drop and recreate anything. And that brings us back to where we began this thread.