Get DDL with dbms_metadata

Did you ever wanted to get the Data Definition Language of an object in Oracle from SQL*Plus.


There is package called DBMS_METADATA which you can use and it gives you exactly that. Use the package to get the DDL in SQL*Plus by passing as first parameter the object type (i.e. TABLE, INDEX…) and second parameter the object name.

SQL> set long 50000

SQL> select dbms_metadata.get_ddl( 'TABLE', 'MY_TABLE' ) from dual;