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