My Oracle Support Banner

12.2 Database Upgrade Has Marked SYSTEM TABLESPACE As LOCAL_TEMP_TABLESPACE For Few Database Users (Doc ID 2385430.1)

Last updated on AUGUST 04, 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

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.