Temp tablespace recreation

For any number of reasons i.e. lack of space on ASM or disks, improperly sized segments, space will need to be reclaimed from the TEMP tablespace.

In my case it was due to a shortage of space, I looked around and there staring at me was over 30Gb of space, from whence I know not. Anyway in recent versions of Oracle (11G onwards), you have the ability to shrink the temp space, unfortunately I was running a version of 10g. So here we go:

Disclaimer: Try out all procedures on a test environment before "going production" with it.

Create temp2 tablespace

Create another temporary tablespace and make it the default tablespace using either sql prompt or grid control/enterprise manager

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘+DATA/test/tempfile/TEMP2 ′ SIZE 2000M, ‘‘+DATA/prod/tempfile/TEMP2 ′ SIZE 2000M'; 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Important: Kill inactive sessions on both nodes in RAC using the old temp tablespace:

Ensure there are no active sessions utilizing the previous temp tablespace:

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

USERNAME                                                                                   SESSION_NUM SESSION_ADDR

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

tommy                                                                                            147 070000020CE379D8

tommy                                                                                             33 070000020FE41DF0

tommy                                                                                          55165 070000020FE1B150

SQL> SELECT SID, SERIAL#, STATUS, LOGON_TIME FROM V$SESSION WHERE SERIAL#=147;

       SID    SERIAL# STATUS                   LOGON_TIME

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

      1600        147 INACTIVE                 18-JUL-12

SQL> SELECT SID, SERIAL#, STATUS, LOGON_TIME FROM V$SESSION WHERE SERIAL#=33;

       SID    SERIAL# STATUS                   LOGON_TIME

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

      1587         33 INACTIVE                 18-JUL-12

SQL> SELECT SID, SERIAL#, STATUS, LOGON_TIME FROM V$SESSION WHERE SERIAL#=55165;

       SID    SERIAL# STATUS                   LOGON_TIME

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

      1527      55165 INACTIVE                 25-JUL-12

Kill the inactive sessions 

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

Drop temp tablespace

SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Recreate Tablespace Temp

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘+DATA/prod/tempfile TEMP.263.789587649′ SIZE 2000M’;

Make TEMP Tablespace default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Drop temporary tablespace temp2

At this point if you run a very busy database you might need to kill inactive sessions and wait till the active sessions on temp2 are done before dropping it.

SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;