Generate DDL with dbms_metadata.get_ddl

To maintenance database, we need generate exact DDL of database objects.
Previously, we approach this target with third party utility such as TOAD or SI Object Browser.
But now Oracle provides offical method - dbms_metadata.get_ddl .

This method can generate almost all types of the database objects of Oracle database.
But by default, it will generate all of the detail option of database object,
which make the DDLs are hard to read.
We can adjust it with dbms_metadata.set_transform_param switch options.

The following SQL statements show the common generation of tables and their indexes.
You should replace &YOUR_TABLE_NAME with your table name pattern or enter them under sqlplus.

set heading off
set line 120
set pages 5000
set long 50000
set heading off
set trimspool off
set feedback off
set longchunksize 800
 
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR',true);
-- change the options of following statements to generate detail information
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
 
spool ddl.sql
 
select
dbms_metadata.get_ddl('TABLE',table_name)
from user_tables
where table_name like '&YOUR_TABLE_NAME';
 
select
dbms_metadata.get_ddl('INDEX',index_name)
from user_indexes
where table_name like '&YOUR_TABLE_NAME';
 
spool off;