ORA-12911: permanent tablespace cannot be temporary tablespace

dbtut

5 years ago

After upgrading to 12.2 databases, the temporary temp tablespace of some users is set to SYSTEM due to bug number 23715518. In this case, when you try to import any schema, you receive the error as follows. This problem was fixed in version 18.1.

ORA-39083: Object type USER:"TEST" failed to create with error:
ORA-12911: permanent tablespace cannot be temporary tablespace

1 2	ORA-39083: Object type USER:"TEST" failed to create with error: ORA-12911: permanent tablespace cannot be temporary tablespace

Solution

In order to solve the problem, it is necessary to find the users whose LOCAL_TEMP_TABLESPACE is set to SYSTEM tablespace and change the temp tablespace of these users to default temp tablespace in the database.

The following query can be used to find users whose LOCAL_TEMP_TABLESPACE is set to SYSTEM tablespace.

SQL> select username from dba_users where local_temp_tablespace='SYSTEM';

1	SQL> select username from dba_users where local_temp_tablespace='SYSTEM';

Change the temp tablespace of users to default temp tablespace:

SQL> alter user TEST LOCAL TEMPORARY TABLESPACE TEMP;

1	SQL> alter user TEST LOCAL TEMPORARY TABLESPACE TEMP;

Or you can create the ALTER command required for all users with the following query.

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

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