How to create a tablespace in Oracle

Create a new tablespace with sensible modern defaults.

This creates a tablespace and a datafile. Set the path or ASM disk group and the sizes for your environment before running.

CREATE TABLESPACE &&tablespace_name
  DATAFILE '/u02/oradata/PRODCDB/&tablespace_name._01.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 32G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- On ASM:
-- CREATE TABLESPACE &tablespace_name DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 32G;

-- Verify (read-only)
SELECT tablespace_name, status, contents, extent_management, segment_space_management
FROM   dba_tablespaces
WHERE  tablespace_name = UPPER('&tablespace_name');