文章內(nèi)容

Oracle如何使用DBMS_METADATA.GET_DDL獲取表，表空間，用戶，視圖等的DDL語

發(fā)布時間: 2012/8/26 16:13:06

-

Oracle如何使用DBMS_METADATA.GET_DDL獲取表，表空間，用戶，視圖等的DDL語句

1.1.顯示設(shè)置：
2.
3./*創(chuàng)建DBMS_METADATA：
4.@?/rdbms/admin/catmeta.sql
5.*/
6.SETSERVEROUTPUTON
7.SETLINESIZE1000
8.SETFEEDBACKOFF
9.setlong999999
10.SETPAGESIZE1000
11./*若希望不顯示storage參數(shù)：
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;