Useful commands for temp table spaces

Show usage:

select TABLESPACE_NAME, BYTES_USED/1024/1024 "MB Used", BYTES_FREE/1024/1024 "MB Free" from V$TEMP_SPACE_HEADER;

List tempfiles:

select file_name, tablespace_name, bytes/1024/1024, autoextensible from dba_temp_files;

Add a tempfile:

ALTER TABLESPACE TempTablespaceName ADD TEMPFILE 'TempfilePathAndName' SIZE 1G;

Modify a tempfile:

alter database tempfile 'TempfilePathAndName' autoextend on next 1G maxsize 31G;