The dbms_metadata package has a few convenient functions and procedures that allow you to generate the SQL to recreate parts of your database. The best-known function is probably dbms_metadata.get_ddl(), but every example of its use that I’ve seen on the Internet seems to use a “select from dual”, e.g.
select dbms_metadata.get_ddl('TABLE','T1','TEST_USER') from dual;
As a consequence of the available examples, I think it’s been commonly assumed that this is the only way to use the get_ddl function – but it’s just a pl/sql function returning a CLOB, and you can use it on any data set. For example, if you want to generate the DDL for all the tablespaces on which a particular user (“test_user”, in my example) has a quota, you could run the following:
set long 20000 set pagesize 0 set linesize 180 set trimspool on break on row skip 1 select dbms_metadata.get_ddl('TABLESPACE',tablespace_name) from dba_ts_quotas where username = 'TEST_USER' ;