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