How to show DDL for a given database object?

Sometimes we need to get the DDL of a database object using sqlplus. There are a few things to have in mind. 

To output the DDL to a file using sqlplus, use the SPOOL command before executing any command:

SPOOL [output file name]

All after the command will be written into to file specified.

When connecting with sqlplus, to get all the data formatted properly, set sqlplus variables first:

set long 1000000 -- large clob
set longchunksize 32000 -- large long columns
set linesize 32000 -- maximum line width.
set trimspool on  -- trim output lines
set heading off  -- no header
set pagesize 0  -- number of lines to output before repeating the header. Set to zero, no header is output.
set newpage none  -- no page feed

Then select the object, ie. specifying the object type, name and shema name:

SELECT DBMS_METADATA.get_ddl('TABLE', 'TABLE_NAME', 'USER_NAME')
  FROM DUAL;

Stop writing to file by setting SPOOL to OFF

SPOOL OFF

Leave a Reply

Your email address will not be published. Required fields are marked *