dbms_metadata.get_ddl的用法

发布时间:2016-12-8 8:22:34 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"dbms_metadata.get_ddl的用法",主要涉及到dbms_metadata.get_ddl的用法方面的内容,对于dbms_metadata.get_ddl的用法感兴趣的同学可以参考一下。

dbms_metadata.get_ddl的用法 开始,只是认为dbms_metadata.get_ddl仅仅能用来获取表结构的语句,其实这个包的功能还是挺多的,差不多所有你希望的对象都能获取:   1.显示设置:   /*创建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:   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.举例:   --表:   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;

上一篇:黑马程序员————高新技术————JDK1.5新特性
下一篇:

相关文章

相关评论