ORA-12901

Looking at a couple of Oracle 9 databases recently, I noticed that, if the SYSTEM tablespace is DICTIONARY managed, you can still use it as the default temporary tablespace (although I’m not suggesting you should do so):

 

SQL> l

  1  select extent_management from dba_tablespaces

  2* where tablespace_name = 'SYSTEM'

SQL> /

 

EXTENT_MANAGEMENT

-----------------

DICTIONARY

 

SQL> alter database

  2  default temporary tablespace system

  3  /

 

Database altered.

 

SQL>

 

However, if you try the same thing in a database where the SYSTEM tablespace has LOCAL management, you get an ORA-12901:

 

SQL> select extent_management from dba_tablespaces

  2  where tablespace_name = 'SYSTEM'

  3  /

 

EXTENT_MANAGEMENT

-----------------

LOCAL

 

SQL> alter database

  2  default temporary tablespace system

  3  /

alter database

*

ERROR at line 1:

ORA-12901: default temporary tablespace must be of

TEMPORARY type

 

SQL>