Author: Thomson McBurney

Page: 1 2

Temporary tablespaces are a great way to manage the space for database sort operations and for storing global temporary tables. In this article I will discuss some great tips related to temporary tablespaces.

Moving datafile of default Temporary Tablespace:

It is a common practice to boost up database performance by moving datafile of your default temporary tablespace to a dedicated disk. However if you try to take it offline or drop default temporary tablespace of database as it will result in error as below

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

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

In order to avoid these errors first you will create a new tablespace and then run above commands.

SQL> create tablespace MYTEMPTBS2
  datafile ‘/export/home1/ora900/oradata/V900/MYTEMPTBS2_01.dbf'
  size 100k
  MYTEMPTBSORARY;
Tablespace created.

SQL> alter database default Temporary tablespace MYTEMPTBS2;
Database altered.

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

Dropping non-default Temporary Tablespace:

Another problem most of us face is that if an attempt is made to drop a temporary tablespace of a user that is not the default temporary tablespace for the database then in such scenario the temporary tablespace of user will not be switched automatically to default temporary tablespace and ORA-00959 error (ORA-00959: tablespace <tablespace_name> does not exist) is found if your operation requires disk sort.

Suppose your database has a default temporary tablespace MYTEMPTBS and non-default temporary tablespace

MYTEMPTBS2. MYTEMPTBS2 is assigned to user SCOTT as the temporary tablespace.

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

TEMPORARY_TABLESPACE
——————————————————
MYTEMPTBS2

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

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

TEMPORARY_TABLESPACE
——————————————————
MYTEMPTBS2

This problem can be solved if you assign a new temporary tablespace to the user SCOTT explicitly.

SQL> alter user scott Temporary tablespace MYTEMPTBS;
User altered.
SQL> select TEMPORARY_TABLESPACE from dba_users where username='SCOTT';

TEMPORARY_TABLESPACE
——————————————
MYTEMPTBS Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   RMAN Recovery Made Easy!!
   Efficiently Tracing Oracle Sessions!!
   Beware of Oracle Outage with database growth!!
   Follow the useful tips for using Oracle on UNIX