How does one create Temporary Tablespaces?
Oracle provides various ways of creating TEMPORARY tablespaces (mainly to provide backward compatibility). One should use the most recent method available: – Prior to Oracle 7.3 – CREATE TABLESPACE temp DATAFILE …; – Oracle 7.3 & 8.0 – CREATE TABLESPACE temp DATAFILE … TEMPORARY; – Oracle 8i and above – CREATE TEMPORARY TABLESPACE temp TEMPFILE …; Oracle 8i and 9i example: SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE ‘/oradata/mytemp_01.tmp’ SIZE 20M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M; For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter. Oracle 9i example using OMF (Oracle Managed Files): SQL> CREATE TEMPORARY TABLESPACE temp; Default Temporary Tablespaces: In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time, or by issuing an “ALTER DATABASE” statement: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp; The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and
