Create tablespace Script

select 'create tablespace '||tablespace_name||' datafile '''||file_name||''' size '||sum(bytes)/1024/1024||'M;'

  2  from dba_data_files

  3  where tablespace_name not in (

  4  'SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS')

  5  group by tablespace_name,file_name;