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