从oracle中提取表结构脚本

时间：2010-06-18 来源：hoolma

从oracle中提取表结构脚本

(引自:http://www.eygle.com/archives/2004/12/oracle9iaioeoeo.html)

从Oracle9i开始Oracle提供了一个新的系统包DBMS_METADATA，可以用于提取对象创建的DDL语句。

这个Package功能极其强大，我们来看看它的使用方法.

1.获得表的创建语句.

SQL> desc dbms_metadata
FUNCTION ADD_TRANSFORM RETURNS NUMBER
...
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
....


SQL> set long 2000

SQL> select dbms_metadata.get_ddl('TABLE','TEST') from dual;


DBMS_METADATA.GET_DDL('TABLE','TEST')

--------------------------------------------------------------------------------


  CREATE TABLE "SYS"."TEST"

   (    "OWNER" VARCHAR2(30),

        "OBJECT_NAME" VARCHAR2(128),

        "SUBOBJECT_NAME" VARCHAR2(30),

        "OBJECT_ID" NUMBER,

        "DATA_OBJECT_ID" NUMBER,

        "OBJECT_TYPE" VARCHAR2(18),

        "CREATED" DATE,

        "LAST_DDL_TIME" DATE,

        "TIMESTAMP" VARCHAR2(19),

        "STATUS" VARCHAR2(7),

        "TEMPORARY" VARCHAR2(1),

        "GENERATED" VARCHAR2(1),

        "SECONDARY" 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 "SYSTEM"


SQL>