DDL: create statements

Since 9i, Oracle provides a package to get the DDL statements, such as for creating tablespace.

SQL> set long 50000
SQL> set heading off
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

In 11.2.0.3, following lines will get the statements for creating indexes and creating user:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off

SQL> select dbms_metadata.get_ddl('INDEX', index_name, 'OWNER_ID')
            from dba_indexes where owner = 'OWNER_ID' and table_name in (......) ;


SQL> select dbms_metadata.get_ddl('USER', 'OWNER_ID') from dual;


In 8i, you have to use Export to extract the same type of statement.