New database versions are always interesting; default parameter values can change; new parameters can be introduced, and new settings can appear. In Oracle 126.96.36.199 and later releases a new user setting, LOCAL_TEMP_TABLESPACE, is available. A local temporary tablespace segregates user temp data, like sorts and hash joins, to a temporary tablespace local to the RAC instance to which a user is connected. In a RAC environment the CREATE LOCAL TEMPORARY TABLESPACE command is used, which creates local bigfile temporary tablespaces on each RAC node. It's used to catch 'spillover' from the normal shared TEMP tablespace and possibly speed execution of processes using larger volumes of TEMP space as network traffic is reduced. The intent from Oracle is that the DBA doesn't need to set this value, but the intent and the execution can sometimes be miles apart.
Upon database creation or upgrade this parameter is set automatically, usually to NULL but occasionally to the default temporary tablespace defined for the database (according to Oracle support this will not create any issues). BUT, in some cases, this is set to SYSTEM, which is NOT a temporary tablespace and since Oracle doesn't catch its own error, users assigned SYSTEM as their local temporary tablespace can create problems when that space needs to be used. One possible problem is that Oracle will realize its mistake and disallow using SYSTEM for temporary segments, which could abruptly terminate a transaction. The other possible problem is that Oracle obeys the setting and numerous temp segments are created in SYSTEM, which can interfere with normal database operation. Neither case is good.
This is a known internal bug at Oracle (BUG 23715518) and has been fixed in Oracle 18c. At the current time no backport patch is available for 12.2.0.x outside of Solaris but there is a workaround that can be implemented. It's a simple script to run:
This script generates the alter user commands, spools them to a file and executes it. The contents of this script are shown below:
set pagesize 0 column name new_value dbname noprint select name from v$database; spool set_local_temp_tablespace_&dbname..sql select 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';' from dba_users where username not in ('XS$NULL'); spool off @set_local_temp_tablespace_&dbname
[For those wondering why XS$NULL is excluded from modification there are two very good reasons. The first is that the XS$NULL account cannot be modified, not even by SYS as SYSDBA. The second is tied to the first as XS$NULL is an account used by Oracle internal processes. Any change to such an account could cause bizarre and inexplicable behavior that could render a database unusable. According to the Oracle documentation XS$NULL is:
"An internal account that represents the absence of a user in a session. Because XS$NULL is not a user, this account can only be accessed by the Oracle Database instance. XS$NULL has no privileges and no one can authenticate as XS$NULL, nor can authentication credentials ever be assigned to XS$NULL."
Any attempt to alter this account generates the following error:
SQL> alter user xs$null profile default; alter user xs$null profile default * ERROR at line 1: ORA-01031: insufficient privileges
So it's a useless endeavor to even try to alter this account. XS$NULL first appeared in Oracle 11.2.0.x.]
If the instance is not part of a RAC cluster then it's assumed nothing adverse can occur; according to the documentation shared storage in the form of ASM needs to be in use before any local temporary tablespaces can be considered. Such assumptions have been in error in the past and it's best to be on the safe side and set each user's local temporary tablespace to the default temporary tablespace.
Such additions can be beneficial, once the kinks have been ironed out. As this is new in 12.2.0.x, and 18c is out and the time between those releases has been short it's a good idea to wait and see what fallout occurs from such a setting. By 19c (which should be out sometime in 2019) there should be enough history with local temporary tablespaces to approach such a configuration with more confidence.
Just like the 'horseless carriage' some ideas take some time to really catch on; local temporary tablespaces may be one of those ideas when in a clustered Oracle environment. Once the DBA gets a good grasp of the concept and adjusts temporary space management to account for a second, instance-local tablespace this could prove to be a landmark change in how Oracle processes data. Only time will tell the tale.