declare
 
      cursor tblspc_cursor is
            SELECT   dtf.tablespace_name, replace(replace(replace(replace(replace(dtf.file_name,'/ora00/','/ora01/'),'/ora02/','/ora01/'),'/ora03/','/ora01/'),'/ora04/','/ora01/'),'/ora07/','/ora01/') file_name,
                     round(sum(nvl(segs.bytes/1024,10240))*1.1) kbytes
            FROM     dba_segments segs, dba_data_files dtf
            WHERE    segs.tablespace_name(+) = dtf.tablespace_name
            and      dtf.file_name = (SELECT min(file_name) FROM dba_data_files WHERE tablespace_name = dtf.tablespace_name)
            and      dtf.tablespace_name not in ('SYSTEM','RBS','TEMP')
            group by dtf.tablespace_name, dtf.file_name;
 
      aux varchar2(30) := '12345';
 
 begin
 
      dbms_output.enable(1000000);
      dbms_output.put_line('spool &p_bd._tablespaces.sql.out');
      dbms_output.put_line(chr(10)||'prompt Criando tablespaces....'||chr(10));
 
      for rec_tblspc in tblspc_cursor loop
          if (aux <> rec_tblspc.tablespace_name and aux <> '12345') then
             dbms_output.put_line('autoextend on maxsize unlimited');
             dbms_output.put_line('extent management local autoallocate');
             dbms_output.put_line('segment space management auto;'||chr(10));
          end if;
          if aux <> rec_tblspc.tablespace_name then
             dbms_output.put_line('create tablespace '||rec_tblspc.tablespace_name);
             dbms_output.put_line('datafile '''||rec_tblspc.file_name||''' size '||rec_tblspc.kbytes||'k reuse');
          else
             dbms_output.put_line(', '''||rec_tblspc.file_name||''' size '||rec_tblspc.kbytes||'k reuse');
          end if;
          aux := rec_tblspc.tablespace_name;
      end loop;
 
      dbms_output.put_line('autoextend on maxsize unlimited');
      dbms_output.put_line('extent management local autoallocate');
      dbms_output.put_line('segment space management auto;'||chr(10));
      dbms_output.put_line('spool off');
 
 end;
 /
spool adm_tablespaces.sql.out

prompt Criando tablespaces....

create tablespace CADOFF_RW
datafile '/ora01/oradata/adm/cadoff_rw01.dbf' size 4646k reuse
autoextend on maxsize unlimited
extent management local autoallocate
segment space management auto;

create tablespace SYSAUX
datafile '/ora01/oradata/adm/sysaux01.dbf' size 374106k reuse
autoextend on maxsize unlimited
extent management local autoallocate
segment space management auto;

create tablespace UNDOTBS1
datafile '/ora01/oradata/adm/undotbs01.dbf' size 26541k reuse
autoextend on maxsize unlimited
extent management local autoallocate
segment space management auto;

spool off