Oracle Tips and Tricks — David Fitzjarrell

February 22, 2008

The Dreaded ORA-01555

Filed under: General — dfitzjarrell @ 15:50

After looking more closely at the blog I mentioned in my last entry I’m finding more and more that this poor soul has offered in the way of advice that is either incomplete or wrong. One of the more glaring examples is his overly simplistic treatment of the source for ORA-01555 errors, reducing it to inadequately sized undo segments. Yes, I know, that’s ONE cause of the dreaded ORA-01555, but it is not the sole cause. Let’s examine this error, its causes and what you can do about them. (Tom Kyte has written on this topic many times, and his in-depth explanations are, in my opinion, the best. I’ve used those to help me write this.)

There are two basic causes of this error, plus a third special case of the second which occurs quite frequently:

1) The undo segments are too small
2) The application fetches across commits (a design flaw)
3) Delayed block cleanout

[LOBs are handled differently but can still generate an ORA-01555; this is discussed toward the end of this post]

The first two are related to the read consistency mechanism Oracle employs; this involves undo segments to reconstruct data as it appeared at the moment the query started. Since UNDO segments are used to provide both read-consistency and to rollback transactions the dreaded ORA-01555 can appear. It is the first situation which is ‘addressed’ by our ‘poor soul’, and ONLY the first situation. According to his blog this is the sole reason for an ORA-01555. Because of that his suggestions, which are valid for that situation alone, are:

* Adjust the undo_retention parameter (for 9i and higher releases of Oracle)
* Increase the size of the UNDO tablespace
* Increase the size of the undo segments [if using manual UNDO management]

You’ll know if this is the likely cause by perusing the alert log for the currently generated ORA-01555 error; you should find a message similar to this:

ORA-01555 caused by SQL statement below (SQL ID: dxswvyyhkkg43, Query Duration=2347 sec, SCN: 0x0915.5ce46fff):

The offending query text will follow. Notice in the error message the elapsed query time is presented:

... Query Duration=2347 sec, ...

If that query time exceeds your setting for undo_retention you have a possible candidate for the corrective steps listed above. [You can find the session responsible for this by querying V$SESSION to retrieve the SID and SERIAL# where SQL_ID or the PREV_SQL_ID is identified by the reported value:

select sid, serial#
from v$session
where sql_id = ‘dxswvyyhkkg43’
or prev_sql_id = ‘dxswvyyhkkg43’;

] Also, if you have the UNDO tablespace set to guarantee the retention (which is usually not necessary as Oracle will do its best to enforce the undo_retention setting) you could run into this same error as subsequent undo segments won’t be able to acquire allocated but currently unused undo space if the undo_retention time hasn’t been met, so it’s not advisable to guarantee the retention in active, heavily used systems. If, on the other hand, that query time is well within the configured undo retention period you’ll need to look elsewhere for the cause. And this is where he stops, leaving one to believe those are the ONLY solutions necessary. Sadly he’s wrong.

[All of this presumes you’re using automatic undo management. If you choose (or are required by version) to use manual undo management then setting OPTIMAL for any undo/rollback segment can be a mistake as Oracle can, and will, resize a rollback/undo segment to the OPTIMAL setting if it hasn’t recently been used by a transaction. Queries don’t usually generate UNDO (see the delayed block cleanout explanation for an exception) so if you’re in the middle of a long-running query and the undo/rollback segment has been transactionally idle Oracle can unexpectedly shrink it down to OPTIMAL which will obliterate any data along with the starting SCN you may have been using to reconstruct the read-consistent image for your query, and as a result generate an ORA-01555. The solution to that problem is to set the OPTIIMAL value to NULL by issuing

alter rollback segment storage(optimal null);

for every rollback segment which has a non-null OPTIMAL setting.]

Since one can receive this error for fetching across commits let’s look at that for a moment. As the ORA-01555 error indicates to you that the UNDO information is no longer available one wonders why a developer would decide to commit inside a loop, as that’s the surest way to generate an ORA-01555. The reasoning for this tactic is to, hopefully, conserve on the use of UNDO/rollback segments, and, unfortunately, it does just that. I consider the loop the entire transaction; commiting in that loop causes Oracle to complete the initial transaction then begin another, thus freeing the UNDO generated up until that point. ‘Freeing’ the UNDO means that Oracle is now free to overwrite it with the next transaction (which is the next ‘section’ of data you want to modify from the original query). Since you can no longer roll back to the original starting point, and you may need one or more data blocks from that point in time, you generate the “snapshot too old” error. Another aspect of committing in a loop is that when the ORA-01555 occurs you leave the database in an unknown state; part of the updates have completed, others have not. I’d hate to be the one to sort through the committed updates and have to manually roll them back to start over again. Oh, and starting over again, using the same code, invites the same problem that stopped the process the last time, so it becomes a never-ending cycle until the code is corrected.

According to the Oracle documentation if you REALLY want to fetch across commits you should use the ROWID pseudocolumn to mimic the CURRENT OF clause of a SELECT .. FOR UPDATE statement. Select the rowid of each row into a variable of the UROWID type, then use the ROWID to identify the current row during subsequent updates and deletes as shown in the following example:

DECLARE
   CURSOR c1 IS SELECT last_name, job_id, rowid
     FROM employees;
   my_lastname   employees.last_name%TYPE;
   my_jobid      employees.job_id%TYPE;
   my_rowid      UROWID;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1 INTO my_lastname, my_jobid, my_rowid;
      EXIT WHEN c1%NOTFOUND;
      UPDATE employees SET salary = salary * 1.02 WHERE rowid = my_rowid;
      -- this mimics WHERE CURRENT OF c1
      COMMIT;
   END LOOP;
   CLOSE c1;
END;
/

Since the fetched rows are not locked by a FOR UPDATE clause other users might unintentionally overwrite your changes, which is another ‘gotcha’ (remember the first ‘gotcha’ is the ORA-01555). And because the extra space Oracle needs to provide read consistency is not released until the cursor is closed this tactic can slow down processing for large updates. You play your cards, you take your chances.

The third is the probably the worst of the group, as it can occur with only a single user on the system performing a SELECT. Yes, a SELECT. How can a select statement generate UNDO and thus an ORA-01555? It’s called Delayed Block Cleanout and it’s a dastardly event because it cannot be eliminated entirely (and this can occur in large databases with SYS-owned tables such as the export/import tables/views [sys.exu9sto, as an example]). Delayed Block Cleanout is caused by a session accessing a block that’s been recently modified and hasn’t been cleaned out yet; the current session needs to check if the modifying session is still active. If the modifying transaction is not active the current session ‘cleans out’ the block so any subsequent sessions accessing that block won’t need to go through the same ‘song and dance’. The cleanout process reads the UNDO header, and determines if the blocks are marked as committed or not. If the changes are committed the session ‘cleans out’ the block removing any transaction-related information for the modified blocks, which generates redo. It also frees the UNDO segments used to modify those blocks. So how can this generate an ORA-01555? The following conditions must be met:

— Data is modified and committed and the blocks are not cleaned out automatically (because they exceed the 10% limit to the SGA block buffer cache).

— The modified blocks are not accessed by another session and won’t be accessed until the current session does so.

— The current session begins a long-running query and starts at SCN t_a, the SCN to roll back to in order to ensure a read-consistent image.

— During this query other sessions modify and commit changes to the database, but don’t touch the blocks this long-running query needs.

— The transaction tables roll around due to the high number of COMMITS and ‘step on’ the transaction entry for SCN t_a.

— The lowest SCN (call it t_b) is now higher than t_a (the read-consistent SCN of the long-running query) preventing a read-consistent image due to the large number of COMMITS.

Bingo, the ORA-01555 rears its ugly head because the query is now attempting to access a block that has not been modified since the query began, but the high number of commits has overwritten the rollback data. Since Oracle is now unsure of whether this block is usable or not (even though it was the query, not some other transaction, which generated the UNDO), it throws the ORA-01555 error.

To prevent such things from happening after large data loads, batch updates or deletes a good idea would be to run DBMS_STATS to access the blocks and clean them out. It’s also a good idea to run that anyway, since you’ve changed the data and such changes may have altered the ‘picture’ Oracle should see for those tables and indexes.

LOB updates are a different matter entirely, and can also throw the ORA-01555 error for the same reason but using a different mechanism. Before images of LOB data are stored in the segment itself provided that MAXEXTENTS has not been reached or the tablespace containing the LOB segments has not filled to capacity. Additionally Oracle keeps PCTVERSION of the storage allocated for LOBs for before images; if PCTVERSION is set to too low of a value older images will be overwritten and, you guessed it, an ORA-01555 error appears. The PCTVERSION setting reserves that percentage of the total number of chunks of LOB data which have been allocated during the update process. Let’s say you have 400 chunks already allocated, PCTVERSION is 5 and you need to update 33 chunks. 33 additional chunks are allocated for the before images of that data. This transaction succeeds and commits, freeing those 33 chunks for the next update. But, wait, PCTVERSION is set to 5 so 5% of 400 (20) of those chunks can’t be touched to preserve the before images. This leaves 13 chunks of the previous allocation available for the next update transaction. If the next update affects 20 chunks then 7 additional chunks need to be allocated to complete that request. For an active table with LOB data this could continue on and on, where Oracle reuses some chunks from a prior update but also allocates additional chunks, until MAXEXTENTS is reached, the tablespace is filled or the series of update transactions comes to an end. A long-running select against that data will probably need those now-overwritten before images and, because they’re no longer available both an ORA-01555 (“snapshot too old, rollback segment too small”) and an ORA-22924 (“snapshot too old”, relating to the PCTVERSION setting) are raised. This occurrence of an ORA-01555 isn’t corrected using the methods listed previously, it’s corrected by increasing the PCTVERSION for the LOB segment in question:

SQL> alter table lobex modify lob (isalob) (pctversion 10);

Table altered.

SQL>

Verifying the setting has been changed:

SQL> select table_name, column_name, pctversion
  2  from user_lobs
  3  where table_name = 'LOBEX'
  4  /

TABLE_NAME                     COLUMN_NAME          PCTVERSION
------------------------------ -------------------- ----------
LOBEX                          ISALOB                       10

SQL>

So what if you query USER_LOBS and find that PCTVERSION is NULL? The LOB has been created with the RETENTION property set which retains the old versions of the LOB data for a period of time, and you’ll see the RETENTION column of the USER_LOBS view populated with the current UNDO_RETENTION value:

SQL> select table_name, column_name, pctversion, retention
  2  from user_lobs
  3  where pctversion is null
  4  /

TABLE_NAME                     COLUMN_NAME          PCTVERSION  RETENTION
------------------------------ -------------------- ---------- ----------
LOBEX2                         ISALOB                                 900

SQL> 

Should this be the case the solution to curing the ORA-01555 is back to the usual remedy of increasing the UNDO_RETENTION, with the added steps of converting the existing LOBS to PCTVERSION and then back to using the UNDO_RETENTION:

SQL> alter system set undo_retention=28800;

System altered.

SQL> alter table lobex modify lob (isalob) (pctversion 10);

Table altered.

SQL> alter table lobex modify lob (isalob) (retention);

Table altered.

SQL>

Without the conversion to PCTVERSION and back the increase in the UNOD_RETENTION is not passed through to the old LOB segments and the problem remains. You can, of course, leave the given LOB segment set to pctversion and manage it that way. The choice is yours, however managing heavily updated LOB data may be easier with RETENTION set rather than configuring the PCTVERSION, even with having to convert it to pctversion for a moment then reverting it back to retention.

Should you need to trace the ORA-01555 error to find its origin you can set the 1555 event to dump the error stack each time an ORA-01555 is encountered:


alter session set events '1555 trace name errorstack forever, level 3';

It might also be a good idea to set the tracefile_identifier so you can more easily find the trace file just generated:


alter session set tracefile_identifier='BAD_1555';

Once you have the trace file you can examine it yourself or upload it to Oracle support with an SR filed for the issue.

A rather nasty bug (2643723) affects LOB segments in Oracle versions 9.2.0.1, 9.2.0.2 and 9.2.0.3 and LOB segments in version 9.2.0.4 (3213101) that use Auto Segment Space Management (ASSM). Concurrent writes to the same LOB segment can create data corruption in that LOB causing an ORA-01555 to be displayed. Metalink Note 253131.1 describes the problem and provides diagnostic code to determine if the error is due to the segment corruption bug.

The ORA-01555 error may have a simple list of causes, but the solutions may be far from simply adjusting the UNDO segment size or resetting the undo_retention parameter. I wish more people offering ‘advice’ would realize this. There would be a lot less myth and a lot more usable information.

February 21, 2008

TEMPORARY (Tablespace) Insanity?

Filed under: General — dfitzjarrell @ 18:24

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. [To be fair the author has changed the title of his blog and has rewritten the post which originally triggered this one, removing the text associating this task with ‘correcting’ a 100% allocated temporary tablespace and replacing it with a better-fitting title.]

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 for all files in any tablespace, especially in older releases of Oracle. As of 9.2 (I believe) the default MAXSIZE is 32G per file, which makes using autoextend much better than in releases prior to that. 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 (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

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/V$SORT_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 (9i and later releases) 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 

V$SORT_USAGE (8.1.5, 8.1.6, 8.1.7) shows the sort segment usage, by user, for all tablespaces:

SQL> desc v$sort_usage
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USER                                               VARCHAR2(30)
 SESSION_ADDR                                       RAW(8)
 SESSION_NUM                                        NUMBER
 SQLADDR                                            RAW(8)
 SQLHASH                                            NUMBER
 TABLESPACE                                         VARCHAR2(31)
 CONTENTS                                           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 for backward compatibility. It was deprecated in 9.2.0.1 and could go away in any future release of Oracle which is why Oracle recommends using the V$TEMPSEG_USAGE view.]

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

--
-- 9i and later
--

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

--
-- 8.1.5, 8.1.6, 8.1.7
--

select segtype, extents, blocks
from v$sort_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.

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
/

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. 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.segtype, u.extents, u.blocks
from v$tempseg_usage u, v$sql s
where s.sql_id = u.sql_id;

For 9iR2 and 9iR1 that use true temporary tablespaces the following modified query returns the 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;

For 8.1.7, 8.1.6 and 8.1.5 the following query returns the information:

select u.username, s.sql_text, u.extents, u.blocks
from v$sort_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; if a segment is listed in V$TEMPSEG_USAGE/V$SORT_USAGE it’s active and the temporary tablespace it resides in cannot be dropped. 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 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.

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. Also, the database default temporary tablespace (10g and later releases) cannot be dropped so it will be necessary in those releases to create a new temporary tablespace, make that the database default, then drop the original temporary tablespace and recreate it as desired (presuming you want your temporary tablespace to be named TEMP [which isn’t a requirement]). 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 and later releases, 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. Oracle can, and will, allocate temp space in all tablespaces in the group provided those resources aren’t exhausted. 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 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.

Temporary tablespaces do not need to reside on standard disks; they can be created using Solid State Disks (SSDs) or, on Exadata, using flash disks, ‘disks’ created from reallocating the resources available to the Smart Flash Cache. This process starts with dropping the original Flash Cache configuration:

CellCLI> drop flashcache

Flash cache ... successfully dropped.

Next up is allocating a smaller flash cache on the storage cell:

CellCLI> create flashcache all size=256M

Flash cache ... successfully created.

Now it’s time to create the new flashdisks from the remaining flash cache storage:

CellCLI> create griddisk all flashdisk prefix=flash

[Disk information from the create command displayed here]

Once all of that work is done it’s time to login to ASM and create a diskgroup containing the new flash disks:

$ sqlplus / as sysasm

...

SQL> crewate diskgroup flashdg external redundancy
  2  disk 'o/*/flash*'
  3  attribute 'compatible.rdbms'='11.2.0.0.0',
  4  attribute 'compatible.asm'='11.2.0.0.0',
  5  'cell.smart_scan_capable'='TRUE',
  6  'au_size'='4M';

Diskgroup created.

SQL>

Now that we have a flash diskgroup creating one or more temporary tablespaces is an easy task, as is assigning those temporary tablespaces to a tablespace group. Performance can dramatically improve using flash-based (or SSD-based) temporary tablespaces/tablespace groups as the disk latency is next to nothing.

Knowing which tablespaces are in which group is information that is readily available through the DBA_TABLESPACE_GROUPS view:

SQL> select group_name, tablespace_name
  2  from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
GROUP1                         TEMP1
GROUP1                         TEMP2

SQL>

If you have several tablespace groups you’ll know which tablespaces each group contains should the lack of space be an issue, although Oracle will tell you which tablespace received the failing space request when a tablespace group is used:

SQL> select a.*
  2  from dba_objects a, dba_objects b, dba_objects c
  3  order by 1;
select a.*
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2

Such messages signify that all tablespaces in the group have exhausted their available space and may need to be extended.

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. And it goes without saying (but I’ll say it anyway) that if you’ve assigned a tablespace group as your database default temporary tablespace you’ll need to change that setting BEFORE reassigning the group members thus eliminating the group.

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');

Kellyn Pot’Vin has written an excellent blog post on getting the most out of temporary tablespace groups; it provides excellent information for those wanting to use temporary tablespace groups and those already doing so.

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.

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

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. With 10g and later releases setting pga_aggregate_target allows Oracle to automatically manage these areas; setting the above mentioned parameters sets the lower size limit to prevent Oracle from possibly undersizing them.) 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.

How can you estimate the amouant of temp space a query may use? V$SQL_PLAN can provide that information if you know the SQL_ID of the query:

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

As long as the query uses the CBO the column will be populated; it is NULL for queries using the RBO.

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 files to autoextend which can consume disk resources at an alarming rate if a large number of queries dump sorts and hash activity to disk) you don’t need to drop and recreate anything. And that brings us back to where we began this thread.

Blog at WordPress.com.