Check

CREATE

CREATE TEMPORARY TABLESPACE &mytemp;

Add Tempfile

ALTER TABLESPACE &mytemp ADD TEMPFILE;

ALTER TABLESPACE &mytemp ADD TEMPFILE '+DATA1' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32767M;

Make Default

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE &mytemp;

Changing Default

If you have changed which TEMP tablespace is the default, then you should make sure that no users have the old TEMP tablespace explicitly defined as their default...

SELECT username, temporary_tablespace

  FROM dba_users

 ORDER by temporary_tablespace, username;

ALTER USER &username TEMPORARY TABLESPACE &mytemp;

DROP

To drop a temporary tablespace...

DROP TABLESPACE &mytemp INCLUDING DATAFILES AND CONTENTS;

Autoextend

Check Autoextend

@/u01/dba/oratemptscheck.sql 

Enable Autoextend

ALTER DATABASE TEMPFILE '/u02/oradata/MYSID/datafile/MYTEMPDF.tmp' AUTOEXTEND ON;

Disable Autoextend

ALTER DATABASE TEMPFILE '/u02/oradata/MYSID/datafile/MYTEMPDF.tmp' AUTOEXTEND OFF;

Bibliography