Oracle TEMP Lifecycle

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 CONTENTS;

NOTE: In 11.2.0.3 there is a bug that prevents DROP TABLESPACE temp... from working (it just hangs). I tried doing an online patch for bug 15913577 but this did not solve the issue. As a workaround I added a new, minimally sized tempfile to the temp tablespace and dropped all other tempfiles.

You cannot drop a TEMP tablespace that is in use. Check what is using it and, if necessary, arrange for sessions to disconnect/reconnect.

@/u01/dba/oratempusagebysession.sql

You can also drop just tempfiles without dropping the whole tablespace...

ALTER TABLESPACE &mytemp DROP TEMPFILE '/u02/MYSID/datafile/o1_mf_temp_mybzkh2z_.tmp';

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