In Oracle, each user can be defined to use a different TEMP tablespace.
This technique is very useful for situation 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 to avoid the inevitable fragmentation that occurs within the TEMP tablespace after a sort operation. As we may know, the Oracle SMON background process is charged with the task of coalescing un-used 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 un-used temporary segments.
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 the DBA 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.
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
Cause: Failed to allocate an extent for temp segment in tablespace. Action: Use 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.
