How to add a datafile to a tablespace in Oracle

Add space to a tablespace by giving it another datafile.

This changes storage. Set the path (or ASM disk group), size, and autoextend to match your environment before running.

ALTER TABLESPACE &&tablespace_name
  ADD DATAFILE '/u02/oradata/PRODCDB/&tablespace_name._NN.dbf' SIZE 1G
  AUTOEXTEND ON NEXT 256M MAXSIZE 16G;

-- On ASM, use a disk group instead of a path:
-- ALTER TABLESPACE &tablespace_name ADD DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 256M MAXSIZE 16G;

-- Verify (read-only)
SELECT tablespace_name, file_name, ROUND(bytes/1024/1024) AS size_mb, autoextensible
FROM   dba_data_files
WHERE  tablespace_name = UPPER('&tablespace_name')
ORDER  BY file_name;