Variuos DDL queries to get TBS creation scripts

Scripts 1:
set lines 132
set pages 200
set long 4000
select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) DDL from dba_tablespaces;

Out put will be like below:
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_ORABAM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_MDS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_SOAINFRA') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_IAS_TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_IAS_ORASDPM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_ORABAM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_MDS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_IAS_ORASDPM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_SOAINFRA') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_IAS_TEMP') from dual;

Script 2:

select 'create tablespace ' || df.tablespace_name || chr(10)
 || ' datafile ''' || df.file_name || ''' size ' || df.bytes/1024/1024
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
 || maxbytes/1024/1024)
 || chr(10)
 || 'default storage ( initial ' || initial_extent
 || decode (next_extent, null, null, ' next ' || next_extent )
 || ' minextents ' || min_extents
 || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents)
 || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name
/


Script 3:

REM
REM                    SCRIPT FOR CREATING TABLESPACES
REM
REM This script must be run by a user with the DBA role.
REM
REM Running this script will in turn create a script to build all the
REM tablespaces in the database.  This created script, create_tablespaces.sql,
REM can be run by any user with the DBA role or with the 'CREATE TABLESPACE'
REM system privilege.
REM

set verify off;
set termout off;
set feedback off;
set echo off;
set pagesize 0;

set termout on;
select 'Creating tablespace build script...' from dual;
set termout off;

create table ts_temp (lineno number, ts_name varchar2(30),
                    text varchar2(800));

DECLARE
   CURSOR ts_cursor IS select   tablespace_name,
                                initial_extent,
                                next_extent,
                                min_extents,
                                max_extents,
                                pct_increase,
                                status
                        from    sys.dba_tablespaces
                        where tablespace_name != 'SYSTEM'
                        and status != 'INVALID'
                        order by tablespace_name;
   CURSOR df_cursor (c_ts VARCHAR2) IS select   file_name,
                                                bytes,
                                                autoextensible,
                                                maxbytes,
                                                increment_by
                                       from     sys.dba_data_files
                                       where    tablespace_name = c_ts
                                         and    tablespace_name != 'SYSTEM'
                                       order by file_name;
   lv_tablespace_name   sys.dba_tablespaces.tablespace_name%TYPE;
   lv_initial_extent    sys.dba_tablespaces.initial_extent%TYPE;
   lv_next_extent       sys.dba_tablespaces.next_extent%TYPE;
   lv_min_extents       sys.dba_tablespaces.min_extents%TYPE;
   lv_max_extents       sys.dba_tablespaces.max_extents%TYPE;
   lv_pct_increase      sys.dba_tablespaces.pct_increase%TYPE;
   lv_status            sys.dba_tablespaces.status%TYPE;
   lv_file_name         sys.dba_data_files.file_name%TYPE;
   lv_bytes             sys.dba_data_files.bytes%TYPE;
   lv_autoextensible    sys.dba_data_files.autoextensible%TYPE;
   lv_maxbytes          sys.dba_data_files.maxbytes%TYPE;
   lv_increment_by      sys.dba_data_files.increment_by%TYPE;
   lv_first_rec         BOOLEAN;
   lv_string            VARCHAR2(800);
   lv_lineno            number := 0;

   procedure write_out(p_line INTEGER, p_name VARCHAR2,
             p_string VARCHAR2) is
   begin
     insert into ts_temp (lineno, ts_name, text) values
            (p_line, p_name, p_string);
   end;

BEGIN
   OPEN ts_cursor;
   LOOP
      FETCH ts_cursor INTO lv_tablespace_name,
                           lv_initial_extent,
                           lv_next_extent,
                           lv_min_extents,
                           lv_max_extents,
                           lv_pct_increase,
                           lv_status;
      EXIT WHEN ts_cursor%NOTFOUND;
      lv_lineno := 1;
      lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name));
      lv_first_rec := TRUE;
      write_out(lv_lineno, lv_tablespace_name, lv_string);
      OPEN df_cursor(lv_tablespace_name);
      LOOP
         FETCH df_cursor INTO lv_file_name,
                              lv_bytes,
                              lv_autoextensible,
                              lv_maxbytes,
                              lv_increment_by;
         EXIT WHEN df_cursor%NOTFOUND;
         if (lv_first_rec) then
            lv_first_rec := FALSE;
            lv_string := 'DATAFILE ';
         else
            lv_string := lv_string || ',';
         end if;
         lv_string:=lv_string||''''||lv_file_name||''''||
                    ' SIZE '||to_char(lv_bytes) || ' REUSE';
         if (lv_autoextensible = 'YES') then
            lv_string:=lv_string||' AUTOEXTEND ON'||
                       ' NEXT '||to_char(lv_increment_by)||
                       ' MAXSIZE '||to_char(lv_maxbytes);
         END IF;
      END LOOP;
      CLOSE df_cursor;
         lv_lineno := lv_lineno + 1;
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' DEFAULT STORAGE (INITIAL ' ||
                      to_char(lv_initial_extent) ||
                      ' NEXT ' || lv_next_extent);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' MINEXTENTS ' ||
                      lv_min_extents ||
                      ' MAXEXTENTS ' || lv_max_extents);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' PCTINCREASE ' ||
                      lv_pct_increase || ')');
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_string := ('   '||lv_status);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string:='/';
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string:='                                                  ';
         write_out(lv_lineno, lv_tablespace_name, lv_string);
   END LOOP;
   CLOSE ts_cursor;
END;
/

spool create_tablespaces.sql
set heading off
set recsep off
col text format a80 word_wrap


select   text
from     ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;