Tablespace Creation Script

select 'create tablespace '||tablespace_name||' datafile '''||file_name||''' size  '||sum(bytes)/1024/1024||'M;'
 from dba_data_files where tablespace_name not in ('SYSTEM','UNDOTBS1','SYSAUX','TEMP','USERS')
 group by tablespace_name,file_name;