How To Add Another Datafile to a Tablespace in Oracle?

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:

SQL> connect HR/fyicenter

SQL> CREATE TABLESPACE my_space 
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space 
  2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE        C:\TEMP\MY_SPACE.DBF               10485760
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF              5242880

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES 
  2  FROM USER_FREE_SPACE 
  3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                6    5177344
MY_SPACE                                5   10354688

This script created one tablespace with two data files.

⇒Managing Oracle Tablespaces and Data Files

⇒⇒Oracle Database Tutorials