ORA-12911: permanent tablespace cannot be temporary tablespace

I got ” ORA-12911: permanent tablespace cannot be temporary tablespace ” error in the Oracle database.

ORA-12911: permanent tablespace cannot be temporary tablespace

Details of error are as follows.

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

permanent tablespace cannot be temporary tablespace

This ORA-12911 errors are related with the Unpublished Bug 23715518.

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

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

SOLUTION 1 :

As a workaround manually change the Local Temporary Tablespace of users back to Temporary Tablespace

Example :-

alter user TEST LOCAL TEMPORARY TABLESPACE TEMP

Or

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

SOLUTION 2 :

For 12.2 version , Install patch 23715518 on Target oracle Home 12.2.0.1 and later Restore the Database from backup and retry Upgrade .

https://updates.oracle.com/download/23715518.html

SOLUTION 3:

For 18c and 19c Below patch can be applied on Target home(18c/19c) and later Restore the Database from backup and retry Upgrade .

https://updates.oracle.com/download/30295137.html

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/