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