yanjun : [Oracle]Get Object's DDL use DBMS

===========================================================

[Oracle]Get Object's DDL use DBMS_METADATA

===========================================================

作者: yanjun0426(http://yanjun0426.itpub.net)
发表于: 2004.12.15 13:48
分类: 初始分类
出处: http://yanjun0426.itpub.net/post/2331/10074
---------------------------------------------------------------

从Oracle 9i 开始，Oracle通过一个新的包 DBMS_METADATA 提供了和对象创建层交互的API。利用这个包可以轻松的抽取相关对象的DDL。

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

如果要抽取一个表的DDL，可以简单的用：

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

其中 FOO 是我们要获取DDL的表名字，默认是当前模式下(要注意对象名要用大写)。在SQL*Plus 中可能还要蚩猻erveroutput

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"

得到的 DDL有的时候似乎太详细了一些--很多Storage / FREELIST之类的语句可能并不是您想要的，可以在当前会话中提交如下语句：

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>

抽取当前模式下所有表的DDL:

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的补丁。

参考信息

http://www.officemail.net.cn/article.asp?id=24
Oracle Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01

原文出处

http://www.dbanotes.net/Oracle/DBMS_METADATA_DDL.htm

http://www.officemail.net.cn/article.asp?id=24

yanjun0426 发表于:2004.12.15 13:48 ::分类: ( 初始分类 ) ::阅读:(101564次) :: 评论 (57)