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

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

 

 

 

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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *