Creating Tablespace

Types of Tablespace
1. SYSTEM Tablespace
- Created with the database
- Contains the data dictionary
- Contains the SYSTEM undo segment
2. Non-SYSTEM Tablespace
- Separate segments
- Eases space administration
- Controls amount of space allocated to a user

Create a tablespace with the CREATE TABLESPACE command:

CREATE TABLESPACE tablespace
[DATAFILES clause]
[MINIMUM EXTENT integer [K|M] ]
[BLOCKSIZE integer [K] ]
[LOGGING | NOLOGGING]
[DEFAULT storage_clause]
[ONLINE | OFFLINE ]
[PERMANENT | TEMPORARY]
[extent_management_clause]
[segment_management_clause]

Example:

CREATE TABLESPACE userdb
DATAFILE '/u01/oradata/userdb01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 5M MAXSIZE 200M;

For locally managed tablespace:

CREATE TABLESPACE userdb
DATAFILE '/u01/oradata/userdb01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;