Syntax for Bigfile Tablespace:
SQL > ALTER TABLESPACE tablespace_name
ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
However, bigfile tablespaces support only one datafile, but that datafile can be very large (up to 128 TB, depending on the block size). So, if your tablespace is truly a bigfile tablespace, you cannot add another datafile to it.
To check if your tablespace is a bigfile tablespace:
SQL > SELECT TABLESPACE_NAME, BIGFILE
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME = 'YOUR_TABLESPACE_NAME';
If BIGFILE = YES, it's a bigfile tablespace (only 1 datafile allowed).
If BIGFILE = NO, it's a smallfile tablespace (you can have multiple datafiles).
If it’s a smallfile tablespace and you want to add more datafiles:
SQL > ALTER TABLESPACE your_tablespace_name
ADD DATAFILE '/path/to/your/datafile02.dbf' SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
If your goal is to increase space in a bigfile tablespace, you should resize the existing datafile:
SQL > ALTER DATABASE DATAFILE '/path/to/your/datafile01.dbf' RESIZE 200G;
Or enable autoextend:
SQL > ALTER DATABASE DATAFILE '/path/to/your/datafile01.dbf'
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
