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

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

1.1.显示设置： 
2. 
3./*创建DBMS_METADATA： 
4.@?/rdbms/admin/catmeta.sql 
5.*/ 
6.SETSERVEROUTPUTON 
7.SETLINESIZE1000 
8.SETFEEDBACKOFF 
9.setlong999999              
10.SETPAGESIZE1000 
11./*若希望不显示storage参数： 
12.EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);  13.*/ 
14.  
15.2.9i R2所支持的45个OBJECT TYPE： 
16. 
17.TypeName                     Meaning 
18.------------------------------ ------------------------------   19.AUDIT_OBJ                     auditsofschemaobjects 
20.AUDIT                         auditsofSQLstatements 
21.ASSOCIATION                   associatestatistics 
22.CLUSTER                       clusters 
23.COMMENT                       comments 
24.CONSTRAINT                     constraints  25.CONTEXT                       applicationcontexts 
26.DB_LINK                       databaselinks 
27.DEFAULT_ROLE                   defaultroles 
28.DIMENSION                     dimensions 
29.DIRECTORY                     directories 
30.FUNCTION                       storedfunctions  31.INDEX                         indexes  32.INDEXTYPE                     indextypes 
33.JAVA_SOURCE                   Javasources 
34.LIBRARY                       externalprocedurelibraries 
35.MATERIALIZED_VIEW             materializedviews 
36.MATERIALIZED_VIEW_LOG         materializedviewlogs 
37.OBJECT_GRANT                   objectgrants 
38.OPERATOR                       operators 
39.OUTLINE                       storedoutlines 
40.PACKAGE                       storedpackages 
41.PACKAGE_SPEC                   packagespecifications 
42.PACKAGE_BODY                   packagebodies 
43.PROCEDURE                     storedprocedures  44.PROFILE                       profiles 
45.PROXY                         proxyauthentications 
46.REF_CONSTRAINT                 referentialconstraint 
47.ROLE                           roles 
48.ROLE_GRANT                     rolegrants 
49.ROLLBACK_SEGMENT               rollbacksegments 
50.SEQUENCE                       sequences  51.SYNONYM                       synonyms 
52.SYSTEM_GRANT                   systemprivilegegrants 
53.TABLE                         tables  54.TABLESPACE                     tablespaces 
55.TABLESPACE_QUOTA               tablespacequotas 
56.TRIGGER                       triggers  57.TRUSTED_DB_LINK               trustedlinks 
58.TYPE                           user-definedtypes  59.TYPE_SPEC                     typespecifications 
60.TYPE_BODY                     typebodies 
61.USER                           users  62.VIEW                           views  63.XMLSCHEMA                     XMLschema 
64.3.举例： 
65. 
66.--表：(注意：表名要大写)   67.SQL>SELECTDBMS_METADATA.GET_DDL('TABLE','T2')FROMDUAL;  68.  
69.DBMS_METADATA.GET_DDL('TABLE','T2')  70.--------------------------------------------------------------------------------   71.  
72. CREATETABLE"TEST"."T2"  73.  (   "OWNER"VARCHAR2(30),  74.        "OBJECT_NAME"VARCHAR2(128),  75.        "SUBOBJECT_NAME"VARCHAR2(30),  76.        "OBJECT_ID"NUMBER,  77.        "DATA_OBJECT_ID"NUMBER,  78.        "OBJECT_TYPE"VARCHAR2(18),  79.        "CREATED"DATE,  80.        "LAST_DDL_TIME"DATE,  81.        "TIMESTAMP"VARCHAR2(19),  82.        "STATUS"VARCHAR2(7),  83.        "TEMPORARY"VARCHAR2(1),  84.        "GENERATED"VARCHAR2(1),  85.        "SECONDARY"VARCHAR2(1)  86.  )PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING 
87. STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645 
88. PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT) 
89. TABLESPACE"EXAMPLE"  90.  
91.  
92.  
93.--索引：   94.SQL>SELECTDBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')FROMDUAL;  95.  
96.DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME')  97.--------------------------------------------------------------------------------   98.  
99. CREATEINDEX"TEST"."IDX_OBJECT_NAME"ON"TEST"."T2"("OBJECT_NAME")  100. PCTFREE10INITRANS2MAXTRANS255 
101. STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645 
102. PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT) 
103. TABLESPACE"EXAMPLE"  104.  
105.  
106.  
107.--主键：   108.SQL>SELECTDBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')FROMDUAL;  109.  
110.DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA')  111.--------------------------------------------------------------------------------   112.  
113. ALTERTABLE"TEST"."PARENT"ADDCONSTRAINT"PK_AA"PRIMARYKEY("BB")  114. USINGINDEXPCTFREE10INITRANS2MAXTRANS255 
115. STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645 
116. PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULT) 
117. TABLESPACE"EXAMPLE" ENABLE  118.  
119.  
120.  
121.  
122.--外键：   123.SQL>SELECTDBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')FROMDUAL;  124.  
125.DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA')  126.--------------------------------------------------------------------------------   127.  
128. ALTERTABLE"TEST"."CHILD"ADDCONSTRAINT"FK_AA"FOREIGNKEY("AA")  129.          REFERENCES"TEST"."PARENT"("BB")ENABLE  130.  
131.  
132.  
133.--表空间：   134.SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX') FROM DUAL;  135.DBMS_METADATA.GET_DDL('TABLESPACE','SYSAUX')  136.------------------------------------------------------------------------   137. 
138.  CREATE TABLESPACE "SYSAUX" DATAFILE  139.  '/u01/Oracle/oradata/orcl/sysaux01.dbf 
140. 
141.  
142.  
143.  
144.--用户：   145.DBMS_METADATA.GET_DDL('USER','SYSTEM')  146.-------------------------------------------------------------------------------   147. 
148.   ALTER USER "SYSTEM" IDENTIFIED BY VALUES 'S:C27C11320D7002613C610B3F5C293AE8  149.  
150.4.综上所述：select dbms_metadata.get_ddl(’OBJECT_TYPE’,'OBJECT_NAME’,'SCHEMA’) from dual|user_xxx|all_xxx|dba_xxx; 
 

服务器租用/服务器托管中国五强！虚拟主机域名注册顶级提供商！15年品质保障！--亿恩科技[ENKJ.COM]