ADD or DROP TEMPORARY tablespace

To drop or add a temporary tablespace follow the steps below


1. create another temporary tablespace

CREATE TEMPORARY TABLESPACE TEMPNEW  TEMPFILE '/vol5/temp03.dbf' size 500m  autoextend off;

2. make it as default temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPNEW;

3. drop the old temporary tablespace

DROP  TABLESPACE TEMP INCLUDING CONTENTS AND datafiles;



* Use the SQL below to add a File to a TEMPORARY Tablespace.



alter tablespace TEMP add tempfile '/vol5/temp04' size 50m reuse autoextend on next 1m maxsize 500m;