14

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?

5
  • 1
    Are you running DBMS_METADATA via SQLplus? Do you have your line width set > 80?
    – David Mann
    Nov 25, 2011 at 14:01
  • I am using SQLPlus. Is there a better utility? Do you mean with 'set linesize 200'? That makes no difference Nov 27, 2011 at 23:08
  • 2
    Seems others had problems as well. Bug in earlier versions of Oracle, and difficulty making DBMS_METADATA play nicely in later versions. asktom.oracle.com/pls/asktom/… My solution is not so great for you. I usually run DBMS_METADATA in a graphical tool (like Toad) and then cut and paste into a text document. Definitely not automatable, but it seems to handle the line endings with CLOBs nicer.
    – David Mann
    Nov 28, 2011 at 15:03
  • Hmmm looks like I might be sticking with the manual way for now then... Thanks for the help and link though :) Nov 29, 2011 at 2:13
  • 1
    @David - You need to set the output column width using COL, as is shown in this example, and it will work. Jun 19, 2012 at 17:55

4 Answers 4

5

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.

1
  • 2
    Note, this writes the file to the server's filesystem. Anyone looking to get the DDL on the client machine, this will not achieve that. Jun 25, 2014 at 14:09
7

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;
1
  • The above script worked! I added a condition, such as object_name = 'SP_NAME', so that I can download any single object.
    – dave
    Apr 9 at 3:31
0

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 );
-1
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
1
  • Welcome on the DBA SE! You can write code using the {} icon in the editor, next time please do it. :-)
    – peterh
    Jan 26, 2021 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.