DBMS_METADATA

DIRECT BY EYGLE.
从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>

2.进一步的，可以通过dbms_metadata获得更全面的DDL语句

Oracle提供一个全面的范例:
$ORACLE_HOME/rdbms/demo/mddemo.sql
包括索引、授权、触发器等所有DDL语句都可以被提取。

SQL> CREATE USER A IDENTIFIED BY A;

用户已创建

SQL> select dbms_metadata.get_ddl('USER','A') from dual;

DBMS_METADATA.GET_DDL('USER','A')
------------------------------------------------------------------

   CREATE USER "A" IDENTIFIED BY VALUES 'AFCC9478DFBF9029'
      TEMPORARY TABLESPACE "TEMP"

有兴趣的可以参考该文件，按照Demo的例子进行测试。

SQL> select dbms_metadata.get_ddl('TABLE','IUFO.IUFO_USERINFO') FROM DUAL;
ERROR:
ORA-31603: object "IUFO.IUFO_USERINFO" of type TABLE not found in schema "SYS"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 2917
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3302
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4189
ORA-06512: at "SYS.DBMS_METADATA", line 326
ORA-06512: at "SYS.DBMS_METADATA", line 410
ORA-06512: at "SYS.DBMS_METADATA", line 449
ORA-06512: at "SYS.DBMS_METADATA", line 615
ORA-06512: at "SYS.DBMS_METADATA", line 1221
ORA-06512: at line 1

no rows selected

SQL> CONN IUFO/UFSOFT4460895
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> CONN IUFO/ufsoft2157923
SP2-0734: unknown command beginning "CONN..." - rest of line ignored.
SQL> CONNECT IUFO/ufsoft2157923
Connected.
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO') FROM DUAL;
SQL> SELECT DBMS_METADATA.GET_DDL('TA." - rest of line ignored. DUAL;
SQL> 734: unknown command beginning "
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO') FROM DUAL;

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

  CREATE TABLE "IUFO"."IUFO_USERINFO"
   (    "USER_ID" VARCHAR2(20) NOT NULL ENA

SQL> COL DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO') FROMAT A100;
SP2-0158: unknown COLUMN option "FROMAT"
SQL> COLUMN DBMS_METADATA.GET_DDL FORMAT A100;
SQL> COL DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO') FORMAT A100;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO') FROM DUAL;

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

  CREATE TABLE "IUFO"."IUFO_USERINFO"
   (    "USER_ID" VARCHAR2(20) NOT NULL ENA

SQL> SET LONG 3000
SQL> SET PAGESIZE 100
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO') FROM DUAL;

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

  CREATE TABLE "IUFO"."IUFO_USERINFO"
   (    "USER_ID" VARCHAR2(20) NOT NULL ENABLE,
        "USER_CODE" VARCHAR2(64) NOT NULL ENABLE,
        "USER_NAME" VARCHAR2(64) NOT NULL ENABLE,
        "PASSWORD" VARCHAR2(128),
        "ROLE" NUMBER(4,0),
        "UNIT_ID" VARCHAR2(64),
        "EMAIL" VARCHAR2(128),
         CONSTRAINT "PK_IUFO_USERINFO" PRIMARY KEY ("USER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IUFO"  ENABLE,
         CONSTRAINT "FK_USER_INFO" FOREIGN KEY ("UNIT_ID")
          REFERENCES "IUFO"."IUFO_UNIT_INFO" ("UNIT_ID") ENABLE
   ) 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 "IUFO"

SQL> CONNECT /AS SYSDBA
Connected.
SQL> SHOW USER
USER is "SYS"
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','IUFO_USERINFO','IUFO') FROM DUAL;

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

  CREATE TABLE "IUFO"."IUFO_USERINFO"
   (    "USER_ID" VARCHAR2(20) NOT NULL ENABLE,
        "USER_CODE" VARCHAR2(64) NOT NULL ENABLE,
        "USER_NAME" VARCHAR2(64) NOT NULL ENABLE,
        "PASSWORD" VARCHAR2(128),
        "ROLE" NUMBER(4,0),
        "UNIT_ID" VARCHAR2(64),
        "EMAIL" VARCHAR2(128),
         CONSTRAINT "PK_IUFO_USERINFO" PRIMARY KEY ("USER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "IUFO"  ENABLE,
         CONSTRAINT "FK_USER_INFO" FOREIGN KEY ("UNIT_ID")
          REFERENCES "IUFO"."IUFO_UNIT_INFO" ("UNIT_ID") ENABLE
   ) 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 "IUFO"

SQL>