Systems Engineering and RDBMS

Archive for June 26th, 2007

Switching temp tablespace and ORA-xxxxx errors

Posted by decipherinfosys on June 26, 2007

You may need to move datafile of your default temp tablespace to a dedicated disk to improve i/o as a part of performance tuning exercise, but it is important to note that you can neither take it offline nor drop default temporary tablespace of database.

SQL> alter tablespace temp offline;
alter tablespace temp offline
*
ERROR at line 1:
ORA-12905: default temporary tablespace cannot be brought OFFLINE

SQL> drop tablespace temp including contents and datafiles;
drop tablespace temp including contents and datafiles
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

You need to follow the steps as mentioned below to achieve this:

SQL> create tablespace TEMP2
2  datafile ‘/export/home1/ora900/oradata/V900/temp2_01.dbf’
3  size 100k
4  TEMPORARY;

Tablespace created.

SQL> alter database default temporary tablespace TEMP2;
Database altered.

SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.

Also, if you drop the temporary tablespace of a user, and this temporary tablespace is not the DEFAULT TEMPORARY TABLESPACE for the database, then user’s temporary tablespace will not automatically switch to the DEFAULT TEMPORARY TABLESPACE.  You will run into “ORA-00959 : tablespace <tablespace_name> does not exist” while doing an operation that requires disk sort say for example CREATE INDEX statement.

Let’s follow that up with an example: Say you have a database having TEMP as default temporary tablespace and TEMP2 as other non-default temporary tablespace that is assigned to user SCOTT as the temporary tablespace.

SQL> select TEMPORARY_TABLESPACE from dba_users where username=’SCOTT';

TEMPORARY_TABLESPACE
——————————
TEMP2

SQL> drop tablespace TEMP2 including contents and datafiles;
Tablespace dropped.

SQL> select TEMPORARY_TABLESPACE from dba_users where username=’SCOTT';

TEMPORARY_TABLESPACE
——————————
TEMP2

You can fix this by explicitly assigning new temporary tablespace to the user SCOTT.

SQL> alter user scott temporary tablespace TEMP;
User altered.

SQL> select TEMPORARY_TABLESPACE from dba_users where username=’SCOTT';
TEMPORARY_TABLESPACE
——————————
TEMP

Posted in Oracle | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 85 other followers