Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Are you running DBMS_METADATA via SQLplus? Do you have your line width set > 80? –  David Mann Nov 25 '11 at 14:01
    
I am using SQLPlus. Is there a better utility? Do you mean with 'set linesize 200'? That makes no difference –  MatthewToday Nov 27 '11 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 '11 at 15:03
    
Hmmm looks like I might be sticking with the manual way for now then... Thanks for the help and link though :) –  MatthewToday Nov 29 '11 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. –  Nick Chammas Jun 19 '12 at 17:55

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
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. –  Andrew Spencer Jun 25 at 14:09

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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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