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

 1.显示设置：/*创建DBMS_METADATA：@?/rdbms/admin/catmeta.sql*/SETSERVEROUTPUTONSETLINESIZE1000SETFEEDBACKOFFsetlong999999             SETPAGESIZE1000/*若希望不显示storage参数：EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);*/ 2.9i R2所支持的45个OBJECT TYPE：TypeName                     Meaning------------------------------ ------------------------------AUDIT_OBJ                     auditsofschemaobjectsAUDIT                         auditsofSQLstatementsASSOCIATION                   associatestatisticsCLUSTER                       clustersCOMMENT                       commentsCONSTRAINT                     constraintsCONTEXT                       applicationcontextsDB_LINK                       databaselinksDEFAULT_ROLE                   defaultrolesDIMENSION                     dimensionsDIRECTORY                     directoriesFUNCTION                       storedfunctionsINDEX                         indexesINDEXTYPE                     indextypesJAVA_SOURCE                   JavasourcesLIBRARY                       externalprocedurelibrariesMATERIALIZED_VIEW             materializedviewsMATERIALIZED_VIEW_LOG         materializedviewlogsOBJECT_GRANT                   objectgrantsOPERATOR                       operatorsOUTLINE                       storedoutlinesPACKAGE                       storedpackagesPACKAGE_SPEC                   packagespecificationsPACKAGE_BODY                   packagebodiesPROCEDURE                     storedproceduresPROFILE                       profilesPROXY                         proxyauthenticationsREF_CONSTRAINT                 referentialconstraintROLE                           rolesROLE_GRANT                     rolegrantsROLLBACK_SEGMENT               rollbacksegmentsSEQUENCE                       sequencesSYNONYM                       synonymsSYSTEM_GRANT                   systemprivilegegrantsTABLE                         tablesTABLESPACE                     tablespacesTABLESPACE_QUOTA               tablespacequotasTRIGGER                       triggersTRUSTED_DB_LINK               trustedlinksTYPE                           user-definedtypesTYPE_SPEC                     typespecificationsTYPE_BODY                     typebodiesUSER                           usersVIEW                           viewsXMLSCHEMA                     XMLschema3.举例：--表：(注意：表名要大写)SQL>SELECTDBMS_METADATA.GET_DDL('TABLE','T2')FROMDUAL; 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>SELECTDBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')FROMDUAL; 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>SELECTDBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')FROMDUAL; DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')--------------------------------------------------------------------------------  ALTERTABLE"TEST"."PARENT"ADDCONSTRAINT"PK_AA"PRIMARYKEY("BB") USINGINDEXPCTFREE10INITRANS2MAXTRANS255 STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645 PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT) TABLESPACE"EXAMPLE" ENABLE    --外键：SQL>SELECTDBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')FROMDUAL; 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;