Dynamically gather DDL for Oracle database tablespaces

Here is a script that I use to dynamically generate the SQL to recreate tablespaces in another database.  I essentially want all tablespaces that are non-system related.  You can model this script by whatever you need to use the package DBMS_METADATA.GET_DDL in your DBA life.

-- @gen_ddl_tbsp.sql
spool tbsp_ddl_gen.sql
set lines 120 head off feed off pages 0 long 1000000
select 'set lines 120 pages 0 long 1000000' from dual;
select 'spool tbsp_ddl.sql' from dual;
select distinct 'SELECT DBMS_METADATA.GET_DDL('
|| ''''
|| 'TABLESPACE'
|| ''''
|| ','
|| ''''
|| tablespace_name
|| ''''
||') || '
||''''
||'
/'
||''''
||' FROM DUAL;'
from dba_tablespaces
where tablespace_name in (select tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','USERS','SYSAUX','UNDOTBS1','UNDOTBS2','TOOLS','TEMP')) order by 1
/
select 'spool off' from dual;
spool off
--@tbsp_ddl_gen.sql  -- This will auto execute the tbsp_ddl_gen.sql script