Following are the steps to add datafile to the tablespace in Oracle

1. Identify the tablespace which is full

Select f.tablespace_name , to_char(t.total_space,'9999,9999') "TOTAL(MB)" , to_char((t.total_space-f.free_space),'9999,9999')"USED(MB)", to_char(f.free_space,'999,999') "FREE(MB)", to_char((round(((t.total_space-f.free_space)/t.total_space)*100)),'999')||'%' PER_USED, to_char((round((f.free_space/t.total_space)*100)),'999')||'%'PER_FREE from (select tablespace_name , round (sum(blocks * ( select value/1024 from v$parameter where name='db_block_size')/1024)) free_space from dba_free_space group by tablespace_name)f , (select tablespace_name , round(sum(bytes/1048576)) total_space from dba_data_files group by tablespace_name ) t where f.tablespace_name=t.tablespace_name ;

2. Check the file location and datafiles naming convention used by existing database

SELECT FILE_NAME,TABLESPACE_NAME,(MAXBYTES)/1024/1024/1024 as MAXSIZE,AUTOEXTENSIBLE FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS' ORDER BY FILE_ID;

3. Add the Datafile at same location and use same file naming convention used by current database:

Syntax:

ALTER TABLESPACE tablespace_name ADD DATAFILE 'LOCATION' SIZE 10M AUTOEXTEND ON MAXSIZE 10G;

Example:

ALTER TABLESPACE USERS add DATAFILE 'E:\ORACLE\ORADATA\ORCL\USERS_40.DBF' SIZE 500m AUTOEXTEND ON NEXT 500m MAXSIZE 16G;