[Oracle]Get Object's DDL use DBMS_METADATA

DESCRIBE dbms_metadata --(output only shows the get_ddl function) 
  
FUNCTION GET_DDL RETURNS CLOB 
  Argument Name                  Type                    In/Out Default? 
  ------------------------------ ----------------------- ------ -------- 
  OBJECT_TYPE                    VARCHAR2                IN 
  NAME                           VARCHAR2                IN 
  SCHEMA                         VARCHAR2                IN     DEFAULT 
  VERSION                        VARCHAR2                IN     DEFAULT 
  MODEL                          VARCHAR2                IN     DEFAULT 
  TRANSFORM                      VARCHAR2                IN     DEFAULT 

SELECT DBMS_METADATA.get_ddl ('TABLE', 'FOO')
  FROM DUAL;

SQL> SET serveroutput on long 999999
SQL> /

DBMS_METADATA.GET_DDL('TABLE','FOO')
-----------------------------------------------------------------------


  CREATE TABLE "SCOTT"."FOO"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM( -
	DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

SQL> SELECT DBMS_METADATA.get_ddl ('TABLE', 'FOO')
  2    FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','FOO')
----------------------------------------------------------------------


  CREATE TABLE "SCOTT"."FOO"
   (    "DUMMY" VARCHAR2(1)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

SQL>

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.iot_type is null or u.iot_type='IOT');

其他问题

在Oracle 9.2.0.5.0 中，存在一个和DBMS_METADATA的相关的Bug。影响Linux x86平台的数据库软件。 错误信息可能类似：

ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLResetAllVars in routine
kuxslResetParams:
LPX-1: NULL pointer
ORA-22921: length of input buffer is smaller than amount requested
ORA-06512: at "SYS.UTL_XML", line 0
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3320
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4148
ORA-06512: at "SYS.DBMS_METADATA", line 458
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

如果遇到类似的错误，可能需要 http://metalink.oracle.com 站点下载编号为2736436的补丁。

参考信息

本文作者

原文出处