Add a datafile to an existing tablespace

Command

ALTER TABLESPACE customer_data
  ADD DATAFILE '/u01/oradata/ORCL/customer_data02.dbf'
  SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 30G;

-- ASM:
ALTER TABLESPACE customer_data
  ADD DATAFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 256M MAXSIZE 30G;

What it does

Grows an existing tablespace's capacity by adding a new datafile.

When to use it

When the tablespace is full and a single file approaches the smallfile limit (32G/64G).

Caution

A smallfile tablespace has a per-file block limit; add a new file if needed. Specify MAXSIZE.