Temp Tablespace

Default Temporary Tablespaces

If you CREATE USER and forget to include a TEMPORARY TABLESPACE clause, Oracle uses the SYSTEM tablespace for that user’s sorts. This hurts performance. 9i addresses this by allowing you to specify a system-wide default temporary tablespace. Specify the DEFAULT TEMPORARY TABLESPACE on the CREATE DATABASE statement. Or, define the new temporary tablespace by the CREATE TEMPORARY TABLESPACE statement, and make it the default by running:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp_ts ;