Saturday, May 19, 2012

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>

No comments: