dbms_metadata的进一步学习

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/more-about-dbmsmetadata/

一开始，只是认为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;

· 【文章发布信息】发表于： 2008-06-23 @ 23:44:22 · ||分类： Study note

留条评论