一开始，只是认为dbms_metadata.get_ddl仅仅能用来获取表结构的语句，其实这个包的功能还是挺多的，差不多所有你希望的对象都能获取：
1.显示设置：
|
1 2 3 4 5 6 7 8 9 10 11 |
/*创建DBMS_METADATA： @?/rdbms/admin/catmeta.sql */ SET SERVEROUTPUT ON SET LINESIZE 1000 SET FEEDBACK OFF set long 999999 SET PAGESIZE 1000 /*若希望不显示storage参数： EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false); */ |
2.9i R2所支持的45个OBJECT TYPE：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
Type Name Meaning ------------------------------ ------------------------------ AUDIT_OBJ audits of schema objects AUDIT audits of SQL statements ASSOCIATION associate statistics CLUSTER clusters COMMENT comments CONSTRAINT constraints CONTEXT application contexts DB_LINK database links DEFAULT_ROLE default roles DIMENSION dimensions DIRECTORY directories FUNCTION stored functions INDEX indexes INDEXTYPE indextypes JAVA_SOURCE Java sources LIBRARY external procedure libraries MATERIALIZED_VIEW materialized views MATERIALIZED_VIEW_LOG materialized view logs OBJECT_GRANT object grants OPERATOR operators OUTLINE stored outlines PACKAGE stored packages PACKAGE_SPEC package specifications PACKAGE_BODY package bodies PROCEDURE stored procedures PROFILE profiles PROXY proxy authentications REF_CONSTRAINT referential constraint ROLE roles ROLE_GRANT role grants ROLLBACK_SEGMENT rollback segments SEQUENCE sequences SYNONYM synonyms SYSTEM_GRANT system privilege grants TABLE tables TABLESPACE tablespaces TABLESPACE_QUOTA tablespace quotas TRIGGER triggers TRUSTED_DB_LINK trusted links TYPE user-defined types TYPE_SPEC type specifications TYPE_BODY type bodies USER users VIEW views XMLSCHEMA XML schema |
3.举例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 |
--表： SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','T2') FROM DUAL; DBMS_METADATA.GET_DDL('TABLE','T2') -------------------------------------------------------------------------------- CREATE TABLE "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) ) 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 "EXAMPLE" --索引： SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME') FROM DUAL; DBMS_METADATA.GET_DDL('INDEX','IDX_OBJECT_NAME') -------------------------------------------------------------------------------- CREATE INDEX "TEST"."IDX_OBJECT_NAME" ON "TEST"."T2" ("OBJECT_NAME") 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 "EXAMPLE" --主键： SQL> SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA') FROM DUAL; DBMS_METADATA.GET_DDL('CONSTRAINT','PK_AA') -------------------------------------------------------------------------------- ALTER TABLE "TEST"."PARENT" ADD CONSTRAINT "PK_AA" PRIMARY KEY ("BB") 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 "EXAMPLE" ENABLE --外键： SQL> SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA') FROM DUAL; DBMS_METADATA.GET_DDL('REF_CONSTRAINT','FK_AA') -------------------------------------------------------------------------------- ALTER TABLE "TEST"."CHILD" ADD CONSTRAINT "FK_AA" FOREIGN KEY ("AA") REFERENCES "TEST"."PARENT" ("BB") ENABLE --表空间： SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','EXAMPLE') FROM DUAL; DBMS_METADATA.GET_DDL('TABLESPACE','EXAMPLE') -------------------------------------------------------------------------------- CREATE TABLESPACE "EXAMPLE" DATAFILE '/oracle/oradata/ora9i/example01.dbf' SIZE 125829120 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO --用户： SQL> SELECT DBMS_METADATA.GET_DDL('USER','TEST') FROM DUAL; DBMS_METADATA.GET_DDL('USER','TEST') -------------------------------------------------------------------------------- CREATE USER "TEST" IDENTIFIED BY VALUES '7A0F2B316C212D67' DEFAULT TABLESPACE "TEST_MSSM" TEMPORARY TABLESPACE "TEMP" |
4.综上所述：select dbms_metadata.get_ddl(‘OBJECT_TYPE’,’OBJECT_NAME’,’SCHEMA’) from dual|user_xxx|all_xxx|dba_xxx;