Setting a New Default Temporary Tablespace in Oracle

Follow below steps to set new temporary tablespace as default one:

• Create new tablespace with different name.
• Mark newly created tablespace as default one.
•  Check running active sessions using existing TEMP tablespace space. If YES then kill that session.
• Drop existing one.

Below are the details of current temporary tablespace:

SQL> select name from v$tempfile;

NAME

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

/u02/test/temp01.dbf

Step 1: Create new temporary tablespace.

SQL> create temporary tablespace TEMP_NEW tempfile '/u02/valentine/temp_new01.dbf' size 200m;

Tablespace created.

Step 2: Mark newly created tablespace as default one.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Database altered.

Step 3: Check any active session using existing TEMP tablespace.

SQL> col username for a12

SQL> col tablespace for a10

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,

a.username,a.osuser,a.status

FROM v$session a,v$sort_usage b

WHERE a.saddr = b.session_addr;  2    3    4 

TABLESPACE   SEGFILE#     SEGBLK#     BLOCKS     SID      SERIAL# USERNAME    OSUSER                  STATUS

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

TEMP        201      128  128      22           59 SYS        oracle                   ACTIVE

Here, I found above active session which was using TEMP tablespace having SID:22 and SERIAL#:59. Kill this session to release the space from TEMP tablespace.

If you try to drop the existing TEMP tablespace without killing the session then this session will take time to drop.

SQL> alter system kill session '22,59' immediate;

System altered.

Step 4: Drop the existing TEMP tablespace and check newly created tablespace.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME

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

/u02/valentine/temp_new01.dbf

Thanks for reading this post ! Please comment if you like this post !