CREATE TABLESPACE

Create a permanent tablespace.

Syntax:

   CREATE [UNDO] TABLESPACE tablespace_name
      DATAFILE Datafile_Options Storage_Options;

Datafile_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.

Storage_Options:

    DEFAULT [COMPRESS|NOCOMPRESS] STORAGE storage_clause
    MINIMUM EXTENT int {K|M}
    BLOCKSIZE int K
    LOGGING | NOLOGGING
    FORCE LOGGING
    ONLINE | OFFLINE
    PERMANENT | TEMPORARY
    EXTENT MANAGEMENT {DICTIONARY |
       LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }
    SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}

Examples

-- With Autoextend:

CREATE TABLESPACE ts_mydemo DATAFILE
'/data/ts_mydemo01.dbf' SIZE 50M,
'/data/ts_mydemo02.dbf' SIZE 64M
logging
autoextend on
next 32m maxsize 2048m
extent management local;

-- With specified datafile sizes:

CREATE TABLESPACE ts_myapp DATAFILE
'/data/ts_myapp01.dbf' SIZE 200M,
'/data/ts_myapp02.dbf' SIZE 500M
logging
autoextend off
extent management local;

-- Undo tablespace

CREATE UNDO TABLESPACE ts_undo01 DATAFILE
'/data/ts_undo01.dbf SIZE 50000M REUSE
autoextend on
RETENTION NOGUARANTEE;

Retention guarantee can also be enabled for UNDO tablespaces with the option RETENTION GUARANTEE, this should be used with caution - it can easily cause updates to fail due to a lack of space in the undo tablespace.

"A place for everything and everything in its place" ~ Isabella Mary Beeton, The Book of Household Management

Related Oracle Commands:

ALTER TABLESPACE
CREATE TEMPORARY TABLESPACE
DROP TABLESPACE

Related Views:

 DBA_DATA_FILES 
 DBA_TABLESPACES                           USER_TABLESPACES
 DBA_TEMP_FILES 
 DBA_TS_QUOTAS                             USER_TS_QUOTAS