dbms_metadata

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'
;