12.2 Database Upgrade Has Marked SYSTEM TABLESPACE As LOCAL_TEMP_TABLESPACE For Few Database Users

(Doc ID 2385430.1)

Last updated on APRIL 16, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Oracle Solaris on SPARC (64-bit)

Symptoms

After Database Upgrade from 12.1.0.1 to 12.2.0.1 Local_temp_tablespace of some database users has been changed to "SYSTEM" tablespace and for some users its NULL 

Example :-

These 22 users are assigned SYSTEM Tablespace as LOCAL_TEMP_TABLESPACE and
others NULL.

SQL> select username from dba_users where local_temp_tablespace='SYSTEM';
USERNAME
-----------------------------------
SYSTEM
SYS
MDDATA
SI_INFORMTN_SCHEMA
ORDDATA
OLAPSYS
ORACLE_OCM
GSMCATUSER
SYSDG
SYSKM
ORDSYS
DBSNMP
SYSBACKUP
DIP
MDSYS
GSMADMIN_INTERNAL
XDB
CTXSYS
ORDPLUGINS
APPQOSSYS
APPS
GSMUSER

SQL> Select local_temp_tablespace,count(*) from dba_users where
local_temp_tablespace is null or local_temp_tablespace='SYSTEM' group by
local_temp_tablespace;

LOCAL_TEMP_TABLESPACE         COUNT(*)
------------------------------         ----------
188                                         

SYSTEM                                     22

 During importing data from this 12cR2 database below errors were reported


Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": userid=system/******** DIRECTORY=DATA_PUMP_DIR_1 DUMPFILE=EXPORT_%U.DMP LOGFILE=EXPORT.log schemas=TEST

Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER:"TGGAPPS" failed to create with error:
ORA-12911: permanent tablespace cannot be temporary tablespace

 

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms