Script out Oracle DDL in an automated fashion

Question

Oracle SQL Developer is able to export DDL through Tools -> Database Export... This works very well, but requires manual intervention.

I know of DBMS_METADATA.get_ddl(), but have found that the export isn't perfect. I ran into issues where the exported DBMS_METADATA DDL wasn't usable without first fixing up problems like breaks in the middle of a keyword, and worse. However, if anyone knows a way of exporting DDL through DMBS_METADATA that can run without manual fixes, that'd be a great solution too.

Basically, I am looking for an automatic/scriptable way to export DDL identical to what is exported through the manual way.

How can I do that?

Nick Chammas · Answer 1 · 2012-06-20T15:54:12.230

The reason you are having problems with dbms_metadata.get_ddl is that it outputs CLOBs which can be up to 4GB in size. By default, SQL*Plus and Oracle SQL Developer truncate long text so they don't trash the client with large gobs of text.

It's very easy to override this behavior in SQL*Plus with a few SET commands and get clean DDL.

The script you need is:

-- Run this script in SQL*Plus.

-- don't print headers or other crap
set heading off;
set echo off;
set pagesize 0;      

-- don't truncate the line output
-- trim the extra space from linesize when spooling
set long 99999;      
set linesize 32767;  
set trimspool on;    

-- don't truncate this specific column's output
col object_ddl format A32000;

spool sys_ddl.sql;

SELECT dbms_metadata.get_ddl(object_type, object_name, owner) || ';' AS object_ddl
FROM DBA_OBJECTS
WHERE 
      OWNER = 'SYS'
  AND OBJECT_TYPE IN (
      'TABLE'
    , 'INDEX'
    , 'SEQUENCE'
    , 'VIEW'
  )
ORDER BY
    OWNER
  , OBJECT_TYPE
  , OBJECT_NAME
;

spool off;

score 5 · Accepted Answer · answered Dec 22 '11 at 20:01

Well, if sqlplus is screwing your dbms_metadata.get_ddl output, why not select the output in a CLOB and write the CLOB to filesystem.

e.g.

DECLARE
    data CLOB;
    objType varchar2(30) := 'TABLE';
    objSchema varchar2(30) := 'SCOTT';
    objName varchar2(30) := 'EMP';
    fname varchar2(256) := objType || '_' || objSchema || '_' || objName || '.sql';
BEGIN
    SELECT dbms_metadata.get_ddl(objType,objName,objSchema) into data from dual;
    DBMS_XSLPROCESSOR.CLOB2FILE(data,'DATA_PUMP_DIR',fname);
END;
/

This should get you correct DDL, w/o the output getting messed up. The only thing is that the script will be created on the DB server and not on the client from where you invoke sqlplus.

The script gets saved in the directory pointed to by the 'DATA_PUPM_DIR' entry on the DB Server. i.e.

select directory_path from all_directories where directory_name like 'DATA_PUMP_DIR';

What's more you can add some sort of iteration over all the tables/indexes etc of a schema, and get a complete schema's DDL in no time. I do it all the time.

score 0 · Answer 3 · answered Jun 16 '15 at 14:04

The following transformations may help. I have not used the DBMS_XSLPROCESSOR.CLOB2FILE method, but I did use these to migrate an Oracle database from Solaris to Linux. I could not use data pump because of the version of Oracle that they were using and the fact that they used XML data types for column data types.

DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'PRETTY',             TRUE );
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',      TRUE );
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',    FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'OID',                FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE',         TRUE );

score -1 · Answer 4 · edited Jan 26 '21 at 15:31

set heading off feedback off trimspool on escape off
set long 1000 linesize 1000 pagesize 0

col SEQDDL format A300

spool tts_create_seq.sql

prompt /* ========================= */

prompt /* Drop and create sequences */

prompt /* ========================= */

select regexp_replace(
 dbms_metadata.get_ddl('SEQUENCE',sequence_name,sequence_owner),
 '^.*(CREATE SEQUENCE.*CYCLE).*$')||';' SEQDDL
 from dba_sequences
 where sequence_owner not in
 (select name
 from system.logstdby$skip_support
 where action=0);

spool off

Script out Oracle DDL in an automated fashion

4 Answers4

Linked