Add a temp tablespace / change the default

Command

-- Create a new temp tablespace:
CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/oradata/ORCL/temp2_01.dbf'
  SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 20G;

-- Change the database default temp:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Add a tempfile to the old temp:
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/ORCL/temp_02.dbf' SIZE 2G;

What it does

Provides temporary space for sort/hash/global temporary table operations; you can move the default to the new tablespace.

When to use it

For ORA-01652 (unable to extend temp segment), a temp bottleneck, or resizing temp.

Caution

After changing the default, do not DROP the old temp immediately if it is in use; drop it once sessions have drained.