Recreating Temporary Tablespace

1- Check existing temp tablespaces:

SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files;

TABLESPACE_NAME            FILE_NAME                      BYTES
------------------------------ --------------------------------------------- ----------
TEMP1                   /Oracle/TEST/db/apps_st/data/temp01.dbf         2097152000
TEMP2                   /Oracle/TEST/db/apps_st/data/temp02.dbf         2097152000

SQL>

2- Check what is the default Temp Tablespace:

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%TEMP%';

PROPERTY_NAME               PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP

SQL>
 

- Check if the assigned default tablespace is a tablespace or a temporary tablespace group! in the above example "TEMP" is a temporary tablespace group.

3- Create new Temporary Tablespace Tempo:

SQL> CREATE TEMPORARY TABLESPACE TEMPO TEMPFILE  '/oracle/PROD/db/apps_st/data/tempo.dbf' SIZE 3000M;

4- Change the default Temp Tablespace to be tempo tablespace:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempo;

5- Drop the old Temporary tablespaces:

> If the old temporary tablespaces are being used by some active sessions, the drop command will hang till the sessions that using those tablespaces were stopped:

-  Check the session that is currently use temporary tablespaces V$SORT_USAGE:

SQL> SELECT USERNAME,SESSION_NUM,TABLESPACE FROM V$SORT_USAGE;

-  If you decided to kill those sessions then you can do the following:

a) SQL> SELECT SID, SERIAL#, STATUS,STATE FROM V$SESSION WHERE SERIAL#=&SESSION_NUM;

b) Terminate unwanted sessions:
 

 SQL> ALTER SYSTEM KILL 'SID,SERIAL#';

c) Drop temp tablespace

SQL> DROP TABLESPACE temp1 INCLUDING CONTENTS AND DATAFILES;SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

6- Recreate Temporary Tablespaces:

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '/oracle/PROD/db/apps_st/data/temp01.dbf' SIZE 6000M AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL;
 

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oracle/PROD/db/apps_st/data/temp02.dbf' SIZE 6000M AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL;

7- Add the Tablespaces to a temporary tablespace group

SQL> ALTER TEMPORARY TABLESPACE temp1 TABLESPACE GROUP TEMP;SQL> ALTER TEMPORARY TABLESPACE temp2 TABLESPACE GROUP TEMP;

- Check that both tablespaces were assigned to the group:
SQL> select * from DBA_TABLESPACE_GROUPS;

- If you need to remove the tablespace from the group use:

SQL> ALTER TEMPORARY TABLESPACE temp1 TABLESPACE GROUP '';

8- Change the default tablespace back again to it's original value:

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

9- Drop the tempo tablespace:

SQL> DROP TABLESPACE tempo INCLUDING CONTENTS AND DATAFILES;

Regards,
Mohamed