以自动化方式编写Oracle DDL脚本

Question

Oracle SQL Developer能够通过"工具 -> 数据库导出..."来导出DDL。这个功能非常好用，但需要手动干预。

我知道可以使用DBMS_METADATA.get_ddl()来导出DDL，但我发现导出的结果并不完美。我遇到了一些问题，比如导出的DBMS_METADATA DDL在使用之前需要修复一些问题，比如关键字中间的断行等等。然而，如果有人知道通过DMBS_METADATA导出DDL的方法，可以在没有手动修复的情况下运行，那也是一个很好的解决方案。

基本上，我正在寻找一种自动/可脚本化的方式来导出与手动方式完全相同的DDL。

我该怎么做呢？

- MatthewToday

---

你是通过SQLplus运行DBMS_METADATA吗？你的行宽设置大于80吗？ - David Mann

我正在使用SQLPlus。有更好的工具吗？你是指 'set linesize 200' 吗？那没有任何区别。 - MatthewToday

似乎其他人也遇到了问题。在早期版本的Oracle中存在错误，并且在后续版本中使用DBMS_METADATA也很困难。我的解决方案对您来说可能不是很好。我通常在图形工具（如Toad）中运行DBMS_METADATA，然后将其剪切并粘贴到文本文档中。肯定无法自动化，但它似乎更好地处理了CLOB的换行符。 - David Mann

嗯，看起来我暂时可能还得依赖手动方式……不过还是谢谢你的帮助和链接 :) - MatthewToday

@David - 你需要使用COL来设置输出列的宽度，就像这个例子中所示，这样就可以正常工作了。 - Nick Chammas

Answer 1

你遇到 dbms_metadata.get_ddl 的问题是因为它输出的是最大可达4GB的 CLOB 类型数据。默认情况下，SQL*Plus和Oracle SQL Developer会截断过长的文本，以避免给客户端带来大量的文本内容。

在SQL*Plus中，只需使用几个 SET 命令即可改变这种行为，并获得干净的DDL。

你需要的脚本如下：

-- 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;

- Nick Chammas

---

以上脚本有效！我添加了一个条件，比如object_name = 'SP_NAME'，这样我就可以下载任何单个对象。 - dave

Answer 2

如果sqlplus破坏了dbms_metadata.get_ddl的输出，为什么不将输出选择为CLOB并将CLOB写入文件系统呢？

例如：

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;
/

这样可以获取到正确的DDL，而不会弄乱输出。 唯一的问题是脚本将在数据库服务器上创建，而不是在你调用sqlplus的客户端上。

脚本保存在数据库服务器上由'DATA_PUPM_DIR'条目指向的目录中。

select directory_path from all_directories where directory_name like 'DATA_PUMP_DIR';

此外，您还可以对模式中的所有表/索引等进行迭代，并在短时间内获取完整的模式DDL。我经常这样做。

- user5294

---

请注意，这将把文件写入服务器的文件系统。如果有人想在客户端机器上获取DDL，这种方法是无法实现的。 - Andrew Spencer

Answer 3

以下的转换可能会有所帮助。我没有使用DBMS_XSLPROCESSOR.CLOB2FILE方法，但我确实使用了这些方法将一个Oracle数据库从Solaris迁移到Linux。由于他们使用的Oracle版本以及他们在列数据类型中使用了XML数据类型，所以我无法使用数据泵。

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 );

- Gandolf989

Answer 4

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

- user222704

---

欢迎来到DBA SE！您可以使用编辑器中的“{}”图标编写代码，请下次这样做。 :-) - peterh