对象元数据的提取

        Oracle 9i通过引入DBMS_METADATA包，简化了从数据库的数据字典内提取对象元数据的过程。

   首先来看一看DBMS_METADATA包中GET_DDL函数的结构：

SQL> desc dbms_metadata

FUNCTION GET_DDL RETURNS CLOB

参数名称                      类型                  输入/输出   默认值?

------------------------------  -----------------------          ------      --------

 OBJECT_TYPE           VARCHAR2                IN

 NAME                        VARCHAR2                IN

 SCHEMA                    VARCHAR2                 IN     DEFAULT

 VERSION                    VARCHAR2                IN     DEFAULT

 MODEL                       VARCHAR2                IN     DEFAULT

 TRANSFORM               VARCHAR2                IN     DEFAULT

    接下来使用DBMS_METADATA从表SCOTT.EMP中提取元数据：

SQL> set pages 0

SQL> set long 1000

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

  CREATE TABLE "SCOTT"."EMP"

   (    "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0),

         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"  ENABLE,

         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")

          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

   ) 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 KEEP)

  TABLESPACE "USERS"

注意：这里一定要set long 1000，因为缺省的long值为80，会导致对象元数据显示不全。

再试试提取PACKAGE的元数据

SQL> set pages 0

SQL> set long 1000

SQL> select dbms_metadata.get_ddl('PACKAGE','DBMS_METADATA','SYS') from dual;

 

DBMS_METADATA.GET_DDL('PACKAGE','DBMS_METADATA','SYS')

--------------------------------------------------------------------------------

  CREATE OR REPLACE PACKAGE "SYS"."DBMS_METADATA" AUTHID CURRENT_USER AS

---------------------------------------------------------------------

-- Overview

-- This pkg implements the mdAPI, a means to retrieve the aggregated

-- definitions of database objects as either XML docs. or their creation DDL,

-- or to submit the XML documents to execute the DDL.

---------------------------------------------------------------------

-- SECURITY

-- This package is owned by SYS with execute access granted to PUBLIC.

-- It runs with invokers rights, i.e., with the security profile of

 

DBMS_METADATA.GET_DDL('PACKAGE','DBMS_METADATA','SYS')

--------------------------------------------------------------------------------

。。。。。。。。。。。（以下输出内容省略）

 

    通过查询Oracle文档：PL/SQL Packages and Types Reference，得到以下内容：

GET_xxx Functions

The following GET_xxx functions let you fetch metadata for objects with a single call:

·         GET_XML

·         GET_DDL

·         GET_DEPENDENT_XML

·         GET_DEPENDENT_DDL

·         GET_GRANTED_XML

·         GET_GRANTED_DDL

Usage Notes

·         These functions allow you to fetch metadata for objects with a single call. They encapsulate calls to OPEN, SET_FILTER, and so on. The function you use depends on the characteristics of the object type and on whether you want XML or DDL.

o        GET_xxx is used to fetch named objects, especially schema objects (tables, views).

o        GET_DEPENDENT_xxx is used to fetch dependent objects (audits, object grants).

o        GET_GRANTED_xxx is used to fetch granted objects (system grants, role grants).

·         For some object types you can use more than one function. For example, you can use GET_xxx to fetch an index by name, or GET_DEPENDENT_xxx to fetch the same index by specifying the table on which it is defined.

·         GET_xxx only returns a single named object.

·         For GET_DEPENDENT_xxx and GET_GRANTED_xxx, an arbitrary number of dependent or granted objects can match the input criteria. You can specify an object count when fetching these objects. (The default count of 10000 should be adequate in most cases.)

·         If the DDL transform. is specified, session-level transform. parameters are inherited.

·         If you invoke these functions from SQL*Plus, you should set the PAGESIZE to 0 and set LONG to some large number to get complete, uninterrupted output.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15203236/viewspace-534847/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15203236/viewspace-534847/