CREATE TABLESPACE WITH AUTO Extend ON

Create a permanent tablespace.

Syntax:

   CREATE [UNDO] TABLESPACE tablespace_name
      DATAFILE Datafile_Options Storage_Options;

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.

Best Regards.