CREATE TEMPORARY TABLESPACE

Create a temporary tablespace.

Syntax:

   CREATE TEMPORARY TABLESPACE tablespace_name
      TEMPFILE Tempfile_Options 
         [EXTENT MANAGEMENT LOCAL] 
            [UNIFORM [SIZE int K | M] ];

Tempfile_Options:
    'filespec' [AUTOEXTEND OFF]
    'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]]

The Autoextend Maxsize clause will default to UNLIMITED if no value is specified.

To create a locally managed tablespace specify 'EXTENT MANAGEMENT LOCAL'. If you set extent management to LOCAL UNIFORM, then you must ensure that each extent contains at least 5 database blocks.

All extents of temporary tablespaces are the same size - if UNIFORM is not defined it will default to 1 MB.

Example

CREATE TEMPORARY TABLESPACE ts_temp TEMPFILE
'/data/temp01.dbf' SIZE 500M,
'/data/temp02.dbf' SIZE 500M
autoextend off
extent management local
UNIFORM SIZE 1m;

There are two kinds of fool. One says, "This is old, and therefore good." And one says, "This is new, and therefore better" - John Brunner, The Shockwave Rider

Related Oracle Commands:

CREATE TABLESPACE
DROP TABLESPACE
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tablespace_name;

Related Views:

 DBA_DATA_FILES 
 DBA_TABLESPACES                           USER_TABLESPACES
 DBA_TEMP_FILES 
 DBA_TS_QUOTAS                             USER_TS_QUOTAS 

 V$TEMPFILE
 V$TEMPORARY_LOBS
 V$TEMPSTAT