New local temporary tablespace in 12.2 defaults to SYSTEM for some users after upgrade

2018-03-22 update: Mike Dietrich blogged on this, with a kind tip o’ the hat. Also updated bug and planned fix info.

Note: big thanks to the invaluable Mike Dietrich for the help with this one.  And a dozen other upgrade issues that we learned about from his blog.  As well as to my awesome colleague Tom Nicholson who identified the issue.

While upgrading databases to 12.2.0.1, we ran into an issue with the new feature called Local Temporary Tablespaces. While a good concept, it has some problems with how it sets itself for users during upgrade, and it seems as if everyone should be checking this post-upgrade and fixing it if necessary.

To quote the Oracle doc on Local Temporary Tablespaces: “Starting with Oracle Database 12c Release 2 (12.2), local temporary tablespaces are available. A local temporary tablespace stores separate, non-shared temp files for every database instance. A local temporary tablespace is used only for spilling temporary results of SQL statements, such as queries that involve sorts, hash aggregations, and joins. These results are only accessible within an instance.”  Also see the Concepts doc version of the explanation. You do not have to create this tablespace on shared disk, even with RAC, as it is intended to be local instance only.

There is a new column in DBA_USERS, LOCAL_TEMP_TABLESPACE, that shows the default local temporary tablespace.  This functions just like the default temporary tablespace, in that it’s a default.  There is also a new database-wide parameter, again just like the default temporary tablespace, which defines what to assign a user if you don’t specify it in the create user command.  See below for some poking around with the new database property.

Now for the problem … while testing some existing code that uses dbms_metadata.get_ddl to generate user create statements, my colleague Tom saw it was now generating a second ALTER for each CREATE USER, which set the default local temporary tablespace.  And then he noticed that for a subset of the users, the alter was trying to set local temp as SYSTEM, which would fail with ORA-12911: permanent tablespace cannot be temporary tablespace.  After some research on the new feature and looking at LOCAL_TEMP_TABLESPACE in dba_users we saw that a significant portion our existing users had their default local temporary tablespace set to the SYSTEM tablespace after upgrade.  All users should have been left NULL for local_temp_tablespace, as is the expected behavior, which would cause them to default to using their default temporary tablespace just as they would have in 12.1.  But that’s not what happened.

select    local_temporary_tablespace  -- 12.2 only
        , temporary_tablespace
        , count(*)
FROM dba_users
group by local_temporary_tablespace, temporary_tablespace
order by 1;

LOCAL_TEMP_TABLESPACE TEMPORARY_TABLESPACE   COUNT(*)
--------------------- -------------------- ----------
SYSTEM                TEMP                         49
TEMP                  TEMP                         12
                      TEMP                         66

Out of 127 users, 66 had the expected behavior of being set to NULL, and therefore defaulting to their default TEMP tablespace. 12 were set to TEMP. And 49 were set to SYSTEM. Which, of course, is not a TEMP tablespace at all.

After some conversation with Mike Dietrich, he noticed that the SPARE9 field found in sys.user$ was set to 0 for all of users that were assigned the SYSTEM tablespace as their local temporary tablespace. It is consistent across multiple different databases that we have upgraded. This seems to be the thing that triggers the incorrect assignment. We opened an SR and found out that there is a (non-visible) bug open with Development on this, Bug 23715518. There is no fix planned at the moment. [Through Mike Dietrich’s blog, learned the full bug name: BUG 23715518 – APPSST12201::SYSTEM TABLESPACE IS MARKED AS LOCAL_TEMP_TABLESPACE AFTER UPGRADE and that it will be fixed in Oracle Database 18c.] The impact of this is not clear but the idea of some types of temp operations (sorts, hash aggregations, and joins) occurring in the SYSTEM tablespace doesn’t seem like a good thing … if this indeed happens, not confirmed by testing at this time.

We decided for now to run a script to change the local temp to match the user’s temp and Oracle agreed with this workaround. It’s a pretty trivial piece of SQL to generate the required script but here it is for convenience …

select 'alter user '||username||' LOCAL TEMPORARY TABLESPACE '||TEMPORARY_TABLESPACE||';'
from dba_users where username not in ('XS$NULL'); 

You could also choose to only do this for users who have their local temp set to SYSTEM and leave the rest alone.  We opted for consistency.

Unrelated to the problem, here’s little bit of futzing with the overall database default for local temp tablespace.

-- the default is null after 12.2 upgrade

> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME like 'DEFAULT_%_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE
---------------------------------------- ----------------------------------------
DEFAULT_PERMANENT_TABLESPACE             USERS
DEFAULT_TEMP_TABLESPACE                  TEMP

-- you can't just set it to TEMP if it's currently null and 
-- TEMP is your default, since it defaults to that anyway

> ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP
*
ERROR at line 1:
ORA-12907: tablespace TEMP is already the default temporary tablespace

-- there's no reason to do this, but it's interesting to try ...
-- we happen to have second temporary tablespace in this database

> ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE PSTEMP;

Database altered.

-- and now it will let you set it to TEMP

> ALTER DATABASE DEFAULT LOCAL TEMPORARY TABLESPACE TEMP;

Database altered.

> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES 
WHERE PROPERTY_NAME like 'DEFAULT_%_TABLESPACE';

PROPERTY_NAME                            PROPERTY_VALUE
---------------------------------------- ----------------------------------------
DEFAULT_LOCAL_TEMP_TABLESPACE            TEMP
DEFAULT_PERMANENT_TABLESPACE             USERS
DEFAULT_TEMP_TABLESPACE                  TEMP
Advertisements