Friday, July 20, 2012

Recreate TEMP tablespace in Oracle

Sometimes you need  to recreate TEMP tablespace in Oracle. For example if it is overgrown and you can't shrink it.  With following sql clauses you can create new TEMP tablespace, start to use it and remove old one:

New TEMP tablespace name in this example is TEMP2 and current TEMP tablespace name is TEMP.

Create new TEMP tablespace:
CREATE SMALLFILE TEMPORARY TABLESPACE "TEMP2" TEMPFILE '<path_to_datafiles>temp02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 6144M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;


Start to use new TEMP tablespace:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP2";



Remove old TEMP tablespace:
(This removing can be done only if nobody isn't using old TEMP tablespace.
You can check if there is still users in old TEMP tablespace with following sql (If you are using RAC you need to run this in all nodes):
select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks from v$session a, v$tempseg_usage b where a.saddr = b.session_addr order by b.tablespace, b.blocks;

If there is users in old TEMP tablespace you need to kill those sessions or wait until they are ending normal way.

After there is no open sessions using old TEMP tablespace you can remove it with following sql:
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

No comments:

Post a Comment