Procedure to add space to tablespace

1. Check current freespace and list all the datafiles which belong to tablespace:-

set lines 120
set pages 200
col file_name for a70

select sum(bytes)/1024/1024 from dba_free_space where tablespace_name='&1';

select file_name,bytes/1024/1024 from dba_data_files where tablespace_name='&1';

2. Before adding new datafile ensure that there is no datafile already exists with same name in the database.

Following query may be used to identify the duplicate name.
SELECT name AS full_name,
       SUBSTR( name, INSTR(name,'/',-1)+1 ) AS  base_name
  FROM v$datafile
 WHERE SUBSTR( name, INSTR(name,'/',-1)+1  ) =  :BaseFileName

3. ":BaseFileName" should be the bound or replaced with the  base filename to be added ( not pathed ) .
e.g. LNSRDBP1_SYSAUX_01.dbf – in exactly the case to be user *( upper/lower as appropriate)


4. Add datafile to tablespace with below sample command:-

Alter tablespace add datafile '' size ;