ORACLE如何使用DBMS_METADATA.GET_DDL获取表，表空间，用户，视图等的DDL语句

 

 
1.显示设置：

/*创建DBMS_METADATA：
@?/rdbms/admin/catmeta.sql
*/
SETSERVEROUTPUTON
SETLINESIZE1000
SETFEEDBACKOFF
setlong999999            
SETPAGESIZE1000
/*若希望不显示storage参数：
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
*/

 
2.9i R2所支持的45个OBJECT TYPE：

TypeName                     Meaning
------------------------------ ------------------------------
AUDIT_OBJ                     auditsofschemaobjects
AUDIT                         auditsofSQLstatements
ASSOCIATION                   associatestatistics
CLUSTER                       clusters
COMMENT                       comments
CONSTRAINT                     constraints
CONTEXT                       applicationcontexts
DB_LINK                       databaselinks
DEFAULT_ROLE                   defaultroles
DIMENSION                     dimensions
DIRECTORY                     directories
FUNCTION                       storedfunctions
INDEX                         indexes
INDEXTYPE                     indextypes
JAVA_SOURCE                   Javasources
LIBRARY                       externalprocedurelibraries
MATERIALIZED_VIEW             materializedviews
MATERIALIZED_VIEW_LOG         materializedviewlogs
OBJECT_GRANT                   objectgrants
OPERATOR                       operators
OUTLINE                       storedoutlines
PACKAGE                       storedpackages
PACKAGE_SPEC                   packagespecifications
PACKAGE_BODY                   packagebodies
PROCEDURE                     storedprocedures
PROFILE                       profiles
PROXY                         proxyauthentications
REF_CONSTRAINT                 referentialconstraint
ROLE                           roles
ROLE_GRANT                     rolegrants
ROLLBACK_SEGMENT               rollbacksegments
SEQUENCE                       sequences
SYNONYM                       synonyms
SYSTEM_GRANT                   systemprivilegegrants
TABLE                         tables
TABLESPACE                     tablespaces
TABLESPACE_QUOTA               tablespacequotas
TRIGGER                       triggers
TRUSTED_DB_LINK               trustedlinks
TYPE                           user-definedtypes
TYPE_SPEC                     typespecifications
TYPE_BODY                     typebodies
USER                           users
VIEW                           views
XMLSCHEMA                     XMLschema

 

3.举例：

--表：(注意：表名要大写)
SQL>SELECT DBMS_METADATA.GET_DDL('TABLE','T2')FROM DUAL;
 
DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------
 
 CREATETABLE"TEST"."T2"
  (   "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)
  )PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING
 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
 TABLESPACE"EXAMPLE"
 
 
 
--索引：
SQL>SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')FROM DUAL;
 
DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')
--------------------------------------------------------------------------------
 
 CREATEINDEX"TEST"."IDX_OBJECT_NAME"ON"TEST"."T2"("OBJECT_NAME")
 PCTFREE10INITRANS2MAXTRANS255
 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
 TABLESPACE"EXAMPLE"
 
 
 
--主键：
SQL>SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')FROM DUAL;
 
DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')
--------------------------------------------------------------------------------
 
 ALTERTABLE"TEST"."PARENT"ADDCONSTRAINT"PK_AA"PRIMARYKEY("BB")
 USINGINDEXPCTFREE10INITRANS2MAXTRANS255
 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645
 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT)
 TABLESPACE"EXAMPLE" ENABLE
 
 
 
 
--外键：
SQL>SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')FROM DUAL;
 
DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')
--------------------------------------------------------------------------------
 
 ALTERTABLE"TEST"."CHILD"ADDCONSTRAINT"FK_AA"FOREIGNKEY("AA")
          REFERENCES"TEST"."PARENT"("BB")ENABLE
 
 
 
--表空间：
SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX')
------------------------------------------------------------------------

  CREATE TABLESPACE "SYSAUX" DATAFILE
  '/u01/oracle/oradata/orcl/sysaux01.dbf

 
 
 
--用户：
DBMS_METADATA.GET_DDL('USER','SYSTEM')
-------------------------------------------------------------------------------

   ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:C27C11320D7002613C610B3F5C293AE8

 
4.综上所述：select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'SCHEMA’) from dual|user_xxx|all_xxx|dba_xxx;

 

From:http://blog.youkuaiyun.com/namesliu/article/details/6646519