RelationalDBDesign RelationalDBDesign


Sorting Operations   «Prev  Next»
Lesson 7Allocating temporary Sorting Space
Objective Allocate and size dedicated to TEMP tablespaces

Allocating Temporary Sorting Space

In Oracle, each user can be defined to use a different TEMP tablespace.
This technique is very useful for situations where a single user (or a related group of users) needs to have exclusive use of their own TEMP tablespace.
Some Oracle DBAs use this technique to segregate common sorting operations and to avoid the inevitable fragmentation that occurs within the TEMP tablespace after a sort operation.
As you may know, the Oracle SMON[1] background process is charged with the task of coalescing unused temporary segments following a sort operation. Unfortunately, this does not always happen instantaneously, and the TEMP tablespace may have small fragments of free space scattered between unused temporary segments.

Assign TEMP tablespaces

One approach is to assign TEMP tablespaces to related Oracle users with the ALTER USER command:
Alter user FRED temporary tablespace TEMP_CUSTOMER;
Alter user SAM   temporary tablespace TEMP_ORDER;

In this fashion, you can control and maintain separate temporary tablespaces and ensure that all SQL sorts are managed efficiently. The downside to this approach is that Oracle cannot grab space from another temporary tablespace.

Size the TEMP tablespace

The TEMP tablespace needs to have enough extents to manage all sorting operations that are requested from Oracle. If there is not enough room in the TEMP tablespace to manage the sort, Oracle will return the ORA-1652 error.
The most disappointing aspect of this problem is the fact that the SQL retrieval has already been completed, and it is not uncommon to see a large query run for several hours before aborting with this message.
ORA-1652: unable to extend temp segment by %s in tablespace %s

You will see this message whenever you have failed to allocate an extent for temp segment in tablespace. To remedy the problem, use the
ALTER TABLESPACE ADD DATAFILE
statement to add one or more files to the tablespace indicated or create the object in other tablespace.
In short, the TEMP tablespace must be allocated large enough to accommodate all concurrent disk sorts that may be requested by the application. The next lesson shows you how to monitor the TEMP tablespace.
[1]SMON: The Oracle system monitor process.