Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted September 13, 2018


By David Fitzjarrell

New database versions are always interesting; default parameter values can change; new parameters can be introduced, and new settings can appear. In Oracle 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:

SQL> @set_local_temp_tablespace

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


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

See all articles by David Fitzjarrell

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM