DBMS_METADATA 包

GET_DDL函数用于获取数据库对表、视图、索引、全文索引、存储过程、函数、包、序列、同义词、约束、触发器等的DDL语句。

MPP环境下不支持使用DBMS_METADATA包。

10.1 数据类型

DBMS_METADATA包中涉及到类型。如下统一说明。

  1. ku$_parsed_iteM记录类型
TYPE ku$_parsed_item IS RECORD (

  item VARCHAR(30),

  value VARCHAR(4000),

  object_row SMALLINT

);

参数详解

  • item

被解析对象属性的名称。

  • VALUE

对象属性值,如果没有就是null。

  • objECT_ROW

对象所在的行数。

ku$_parsed_items为ku$_parsed_item类型的索引表:

TYPE ku$_parsed_items IS TABLE OF ku$_parsed_item INDEX BY INT;
  1. ku$_ddl记录类型
TYPE ku$_ddl IS RECORD(

   ddlText CLOB,

   parsedItem ku$_parsed_items

);

参数详解

  • ddlText

DDL语句。

  • parsedItem

对象属性。

ku$_ddls为ku$_ddl类型的索引表:

TYPE ku$_ddls IS TABLE OF ku$_ddl INDEX BY INT;
  1. ku$_multi_ddl记录类型
TYPE ku$_multi_ddl IS RECORD(

   object_row NUMBER,

   ddls ku$_ddls

);

参数详解

  • object_row

对象所在的行数。

  • ddls

ku$_ddls类型。

ku$_multi_ddLs为ku$_multi_ddL类型的索引表:

TYPE ku$_multi_ddls IS TABLE OF ku$_multi_ddl INDEX BY INT;

10.2 相关方法

  1. GET_DDL

获取指定对象元数据中的DDL语句。

语法如下:

FUNCTION GET_DDL(

  object_type IN VARCHAR(30),

  name IN VARCHAR(128),

  schname IN VARCHAR(128) DEFAULT NULL

) RETURN CLOB

参数详解

  • object_type

对象类型。包括表、视图、物化视图、索引、全文索引、存储过程、函数、包、目录等,详情请见open参数详解。其中,object_type只能为大写。

  • name

对象名称,区分大小写。

  • schema

模式,默认是当前用户模式。

返回值

以ddl返回对象元数据中的DDL语句。

错误处理

INVALID_ARGVAL:如果输入参数中存在空值或非法值。

OBJECT_NOT_FOUND:如果指定的对象在数据库中不存在。

  1. ”OPEN”

打开对象类型的句柄

语法如下:

FUNCTION OPEN (

   object_type IN VARCHAR2

) RETURN NUMBER;

参数详解

  • object_type

可返回的对象类型名称,合法的类型名称见下表。

表 OPEN函数可返回的对象类型名称
类型名称 含义 属性 说明
CLASS 类类型 SN 默认返回类头类体
CLASS_HEAD 类型名 SN
CLASS_BODY 类型体 SN
COL_STATISTICS 列统计 D
COMMENT 注释 D
CONSTRAINT 约束 SND 不包括聚集主键和非空约束
CONTEXT 上下文 N
CONTEXT_INDEX 全文索引 N
DATABASE_EXPORT 数据库下的所有对象 H 库级导出
DB_LINK 数据库链接 SN 因此类对象具有所有者,因此将其视为模式级对象。
对于公有连接,它们的所有者是PUBLIC;对于私有链接,它们的创建者就是它们的所有者
DIRECTORY 目录 N
DOMAIN N
FUNCTION 存储函数 SN
INDEX 索引 SND 不包括系统内部定义的索引
INDEX_STATISTICS 索引统计 D
JOB 任务 N
OBJECT_GRANT 对象权限 DG
PACKAGE SN 默认返回包头包体
PKG_SPEC 包头 SN
PKG_BODY 包体 SN
POLICY 策略 D
PROCEDURE 存储过程 SN
ROLE 角色 N
ROLE_GRANT 角色权限 G
SCHEMA_EXPORT 模式下的所有对象 H 模式级导出
SEQUENCE 序列 SN
SYNONYM 同义词 见说明 私有同义词为模式对象,公有同义词为命名对象
SYSTEM_GRANT 系统权限 G
TABLE SN
TABLE_STATISTICS 表统计信息 D
TABLE_EXPORT 表及与其相关的元数据 H 表级导出
TABLESPACE 表空间 N
TRIGGER 触发器 SND
USER 用户 N
VIEW 视图 SN
TYPE 用户自定义类型 SN
MATERIALIZED_VIEW 物化视图 SN
MATERIALIZED_VIEW_LOG 物化视图日志 SN

注:属性列中,S表示模式对象,N表示命名的对象,D表示依赖对象,G表示被授权的对象,H表示包含不同类型的对象。

返回值

不透明的句柄。该句柄将用于SET_FILTER, SET_PARSE_ITEM, FETCH_xxx, and CLOSE。

错误处理

INVALID_ARGVAL:参数为NULL,或者参数值非法。

  1. ”CLOSE”

关闭OPEN打开的句柄,并清理相关环境。

语法如下:

PROCEDURE CLOSE (

   handle IN NUMBER

);

参数详解

handle

OPEN函数返回的句柄。

错误处理

INVALID_ARGVAL:参数为NULL,或者参数值非法。

  1. FETCH_DDL()

该函数返回符合OPEN,SET_FILTER,SET_PARSE_ITEM设定条件的对象元数据。

语法如下:

FUNCTION FETCH_DDL (

    handle IN NUMBER

)RETURN ku$_ddls;

参数详解

  • handle

OPEN函数返回的句柄。

返回值

对象的元数据或者当所有对象都已返回后返回NULL。

使用说明:

从表ku$_ddls中返回ddl语句,表ku$_ddls的每一行在ddlText列包含一条ddl语句;如果使用解析的话,解析出的对象属性将从parsedItem列返回。

错误处理

INVALID_ARGVAL:参数为NULL,或者参数值非法。

  1. FETCH_CLOB()

该函数返回符合OPEN,SET_FILTER设定条件的对象元数据。

语法如下:

FUNCTION FETCH_CLOB(

   handle IN INT

)RETURN CLOB;

参数详解

  • handle

OPEN函数返回的句柄。

返回值

对象的元数据或者当所有对象都已返回后返回NULL。

使用说明:

将对象以clob返回。

异常:

INVALID_ARGVAL:参数为NULL,或者参数值非法。

  1. GET_GRANTED_DDL()

该函数用于返回对象的授权语句。

语法如下:

FUNCTION GET_GRANTED_DDL (

  object_type IN VARCHAR2,

  grantee IN VARCHAR2,

  object_count IN NUMBER DEFAULT 10000

)RETURN CLOB;

参数详解

  • object_type

对象类型。参数要与 OPEN 的对象类型参数相同, 不能为heterogeneous 对象类型。

  • grantee

被赋予权限的对象。

  • object_count

返回对象个数的最大值。

返回值

以ddl返回对象的元数据

异常:

INVALID_ARGVAL:参数值为null,或者参数值非法。

OBJECT_NOT_FOUND:指定的对象数据库中不存在。

  1. GET_DEPENDENT_DDL()

该函数用于返回依赖对象的ddl语句。

语法如下:

FUNCTION GET_DEPENDENT_DDL (

   object_type IN VARCHAR2,

   base_object_name IN VARCHAR2,

   base_object_schema IN VARCHAR2 DEFAULT NULL,

   object_count IN NUMBER DEFAULT 10000

)RETURN CLOB;

参数详解

  • object_type

对象类型。参数要与 OPEN 的对象类型参数相同, 不能为heterogeneous 对象类型。

  • base_object_name

基对象名称。内部使用BASE_OBJECT_NAME过滤器过滤。

  • base_object_schema

基对象模式。内部使用BASE_OBJECT_SCHEMA过滤器过滤。

  • object_count

返回对象个数的最大值。

返回值

以ddl返回对象的元数据。

异常:

INVALID_ARGVAL:参数值为null,或者参数值非法。

OBJECT_NOT_FOUND指定的对象数据库中不存在。

  1. SET_FILTER()

该过程用于过滤待返回的对象。

语法如下:

PROCEDURE SET_FILTER (

  handle IN NUMBER,

  name IN VARCHAR2,

  value IN VARCHAR2

);

参数详解

  • handle

句柄,由DBMS_METADATA.OPEN输出。

  • name

过滤器的名称,详见下表。

  • value

过滤器的值,与name相对应,详见下表。

表 SET_FILTER过程中的过滤器名称
对象类型 名称 数据类型 含义
可命名对象 NAME 文本 通过对象名过滤
可命名对象 NAME_EXPR 文本表达式 通过对象名的where表达式过滤,满足条件的返回
可命名对象 EXCLUDE_NAME_EXPR 文本表达式 通过对象名的where表达式过滤,不满足条件的返回
模式对象 SCHEMA 文本 通过模式名过滤,如果是公有同义词则指定其模式为PUBLIC
模式对象 SCHEMA_EXPR 文本表达式 通过对象名的where表达式过滤,默认为当前模式
表、索引、表级导出 TS 文本 通过表空间名过滤
表、索引、表级导出 TABLESPACE_EXPR 文本表达式 通过表空间名的where表达式过滤
依赖对象 BASE_OBJECT_NAME 文本 通过基对象名过滤。
依赖对象 BASE_OBJECT_SCHEMA 文本 通过基对象模式过滤。如果使用BASE_OBJECT_NAME过滤,默认是当前模式。
依赖对象 BASE_OBJECT_NAME_EXPR 文本表达式 通过基对象名的where表达式过滤,满足条件的返回。不适用于模式和库级触发器。
依赖对象 EXCLUDE_BASE_OBJECT_NAME_EXPR 文本表达式 通过基对象名的where表达式过滤,不满足条件的返回。不适用于模式和库级触发器。
依赖对象 BASE_OBJECT_SCHEMA_EXPR 文本表达式 通过基对象模式的where表达式过滤。
依赖对象 BASE_OBJECT_TYPE 文本 通过基对象的对象类型过滤。
依赖对象 BASE_OBJECT_TYPE_EXPR 文本表达式 通过基对象的对象类型表达式过滤。
依赖对象 BASE_OBJECT_TS 文本 通过基对象的表空间过滤。
依赖对象 BASE_OBJECT_TS_EXPR 文本表达式 通过基对象的表空间where表达式过滤。
被授权对象 GRANTEE 文本 被授权的用户或角色。 AUDIT_obj只能过滤跟grantee有关的其他grant过滤器不适用。
被授权对象 ROLE_NAME 文本 通过权限或角色的名称过滤
被授权对象 ROLE_NAME_EXPR 文本表达式 通过权限或角色的where表达式名称过滤
被授权对象 GRANTEE_EXPR 文本表达式 通过被授权者名称的where表达式过滤,符合条件的返回
被授权对象 EXCLUDE_GRANTEE_EXPR 文本表达式 通过被授权者名称的where表达式过滤,不符合条件的返回
对象权限(OBJECT_GRANT ) GRANTOR 文本 通过授权者过滤,对于系统用户(sysdba sysauditor syssso)赋予的权限在权限表里面ID记录的都是-1,所以设定了一个grantor 叫"$SUPER",当filter中设定grantor为$SUPER时,系统用户赋予的权限将全部返回而不区分具体是哪个系统用户
所有对象 CUSTOM_FILTER 文本 用户自定义过滤,要求填写完整的where子句,如“NAME = ‘T1’”
模式级导出 SCHEMA 文本 通过模式名过滤
模式级导出 SCHEMA_EXPR 文本表达式 通过模式名where表达式过滤,有以下两种情况:1.获取模式对象; 2.获取依赖于此模式的对象。默认情况下,将选中当前用户的对象。
表级导出 SCHEMA 文本 通过模式名过滤
表级导出 SCHEMA_EXPR 文本表达式 通过模式名where表达式过滤,有以下两种情况:1.获取模式下的表;2.获取依赖于表的对象。默认情况下,将选中当前用户的对象。
表级导出 NAME 文本 通过表名过滤出表及依赖表的对象
表级导出 NAME_EXPR 文本表达式 通过表名的where表达式过滤出表及依赖表的对象

注:约束的BASE_OBJECT_TYPE 是UTAB,注释的BASE_OBJECT_TYPE为TABLE或者VIEW。索引、OBJECT_GRANT的BASE_OBJECT_TYPE为UTAB,TABLE_STATISTICS,INDEX_STATISTICS,COL_STATISTICS的BASE_OBJECT_TYPE分别对应为T、I、C。

错误处理

INVALID_ARGVAL:参数为NULL,或者参数值非法。

INVALID_OPERATION:非法操作。在调用FETCH_xxx之后,不允许再调用 SET_FILTER 。

INCONSISTENT_ARGS:参数不一致,包括如下状况:

  • 过滤器名字与DBMS_METADATA.OPEN中指定的类型不匹配。
  • 过滤器的名字与object_type_path不匹配。
  • object_type_path与DBMS_METADATA.OPEN中指定的类型不匹配。
  • 输入的过滤器的value与期待的数据类型不匹配。
  1. SET_PARSE_ITEM()

该过程用于解析对象的属性。

语法如下:

PROCEDURE SET_PARSE_ITEM (

   handle IN NUMBER,

   name IN VARCHAR2,

   object_type IN VARCHAR2 DEFAULT NULL

);

参数详解

  • handle

OPEN函数返回的句柄。

  • name

被解析对象属性的名称,具体的可被解析的对象属性名称见下表。

  • object_type

应用于不同对象的集合,不设置时解析所有的对象。

表 SET_PARSE_ITEM可解析的对象属性
对象类型 属性名称 含义
所有对象 VERB 如果调用 FETCH_DDL,表ku$_ddls每一行ddltext列中的动词将被返回. 如果ddlText是 SQL DDL 语句, SQL 中的动词 (如 CREATE, GRANT, AUDIT) 被返回。如果 ddlText 是过程(如DBMS_METADATA.FETCH_DDL()) 那么 package.procedure-name 被返回
所有对象 OBJECT_TYPE 如果调用 FETCH_DDL, ddlText是 SQL DDL 语句且包含动词 CREATE 或者ALTER,DDL语句中的对象类型将被返回 (如 TABLE,PKG_BODY等等)。否则“表OPEN函数可返回的对象类型名称”中的对象类型被返回
模式对象 SCHEMA 返回对象的模式,如果不是模式对象则没有返回值
命名的对象 NAME 返回对象的名称,如果不是命名的对象则没有返回值
表、表数据、索引 TABLESPACE 返回对象表空间名称,如果是分区表则返回默认的表空间。对于 TABLE_DATA 对象, 总是返回行所在的表空间
触发器 ENABLE 返回触发器是禁用还是启用状态
依赖对象 BASE_OBJECT_NAME 返回基对象名称,如果不是依赖对象则没有返回值
依赖对象 BASE_OBJECT_SCHEMA 返回基对象所属的模式,如果不是依赖对象则没有返回值
依赖对象 BASE_OBJECT_TYPE 返回基对象所属类型,如果不是依赖对象则没有返回值

注:对象类型是可解析的对象属性所适用的范围;BASE_OBJECT_NAME,BASE_OBJECT_SCHEMA,BASE_OBJECT_TYPE这三个属性只解析约束、索引、全文索引、表级/视图级触发器。

使用说明:

FETCH_XXX函数可以返回对象的ddl语句,使用SET_PARSE_ITEM则可以返回对象的各个属性,可以多次调用SET_PARSE_ITEM来解析和返回多个解析项,返回的解析项存于表ku$_parsed_items中。

错误处理

INVALID_ARGVAL:参数为NULL,或者参数值非法。

INVALID_OPERATION:非法操作。SET_PARSE_ITEM 只能用于FETCH_xxx函数之前。第一次调用FETCH_xxx 后,不允许再调用SET_PARSE_ITEM。

  1. GET_QUERY()

该函数用于获取查询文本。

语法如下:

FUNCTION GET_QUERY (

  handle IN NUMBER)

RETURN VARCHAR2;

参数详解

handle: OPEN函数返回的句柄。

返回值

将被用于fenth_xxx函数的查询文本。

错误处理

INVALID_ARGVAL:参数值为null,或非法。

  1. SET_COUNT()

该过程用于指定一次fetch_xxx函数调用所返回对象个数的最大值。

语法如下:

PROCEDURE SET_COUNT (

  handle 	IN NUMBER,

  value 	IN NUMBER

);

参数详解

  • handle

OPEN函数返回的句柄。

  • value

设定的最大值。

错误处理

INVALID_ARGVAL:参数值为null或非法;

INVALID_OPERATION:非法操作。SET_COUNT只能用于第一次调用 FETCH_xxx函数之前,第一次调用FETCH_xxx 后,不允许再调用SET_COUNT。

  1. SET_TRANSFORM_PARAM()

该过程用于格式化过滤会话级约束或存储选项的定义。

语法如下:

PROCEDURE SET_TRANSFORM_PARAM (

  transform_handle         IN INT,

  name            		   IN VARCHAR,
  
  value					   IN BOOLEAN DEFAULT TRUE,
  
  object_type			   IN VARCHAR DEFAULT NULL

);

参数详解

  • transform_handle

会话句柄,目前仅支持DBMS_METADATA.SESSION_TRANSFORM,表示定义的格式化应用于当前会话。

  • name

表示待过滤的约束或存储选项的名称,可取值CONSTRAINTS或STORAGE:CONSTRAINTS表示约束;STORAGE表示存储选项。与参数VALUE配合使用。

  • value

表示是否过滤约束或存储选项的定义,可取值TRUE或FALSE:TRUE表示过滤;FALSE表示不过滤。缺省为TRUE。

  • object_type

应用本格式化定义的对象类型,由于transform_handle只支持会话级,因此该参数暂无实际作用。

错误处理

INVALID_ARGVAL:参数值为null或非法。

10.3 错误处理

错误、异常情况释义:

INVALID_ARGVAL:非法的参数数据。

OBJECT_NOT_FOUND:未找到对象。

INVALID_OPERATION:无效的过程/函数调用顺序。

INCONSISTENT_ARGS:对象类型与过滤器不匹配。

10.4 举例说明

使用包内的过程和函数之前,如果还未创建过系统包。请先调用系统过程创建系统包。

SP_CREATE_SYSTEM_PACKAGES (1,'DBMS_METADATA');

例1 获取表和存储过程的DDL语句。

--建表

CREATE TABLE "SYSDBA"."T1"

(

"C1" CHAR(10) NOT NULL,

"C2" CHAR(10),

CONSTRAINT "PK" PRIMARY KEY("C1")) STORAGE(ON "MAIN", CLUSTERBTR);

--获取对象的ddl语句

SELECT DBMS_METADATA.GET_DDL('TABLE','T1','SYSDBA');

执行结果:

CREATE TABLE "SYSDBA"."T1"

(

"C1" CHAR(10) NOT NULL,

"C2" CHAR(10),

CONSTRAINT "PK" PRIMARY KEY("C1")) STORAGE(ON "MAIN", CLUSTERBTR);

例2 使用DBMS_METADATA程序化接口获取元数据。

--建表

CREATE TABLE "RESOURCES"."TIMECARDS"

(  "EMPLOYEE_ID" NUMBER(6,0),

   "WEEK" NUMBER(2,0),

   "JOB_ID" VARCHAR2(10),

   "HOURS_WORKED" NUMBER(4,2),  
   FOREIGN KEY ("EMPLOYEE_ID")

   REFERENCES "RESOURCES"."EMPLOYEE" ("EMPLOYEEID")

) STORAGE(ON "MAIN", CLUSTERBTR) ;

--建函数

CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS

-- 定义局部变量

h NUMBER; --handle returned by OPEN

th NUMBER; -- handle returned by ADD_TRANSFORM

doc CLOB;

BEGIN

-- 指定对象类型

h := DBMS_METADATA."OPEN" ('TABLE');

-- 使用过滤器返回特定的对象

DBMS_METADATA.SET_FILTER(h,'SCHEMA','RESOURCES');

DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');

-- 获取对象

doc := DBMS_METADATA.FETCH_CLOB(h);

-- 释放资源

DBMS_METADATA."CLOSE" (h);

RETURN doc;

END;

/

SELECT get_table_md;

查询结果如下:

CREATE TABLE "RESOURCES"."TIMECARDS"

(

"EMPLOYEE_ID" NUMBER(6,0),

"WEEK" NUMBER(2,0),

"JOB_ID" VARCHAR2(10),

"HOURS_WORKED" NUMBER(4,2),

FOREIGN KEY("EMPLOYEE_ID")

REFERENCES "RESOURCES"."EMPLOYEE"("EMPLOYEEID")

) STORAGE(ON "MAIN", CLUSTERBTR) ;

例3 使用DBMS_METADATA浏览接口获取元数据。

SELECT DBMS_METADATA.GET_DDL('TABLE','TIMECARDS','RESOURCES');

查询结果同例2。

例4 获取多个对象。

DROP TABLE my_metadata;

CREATE TABLE my_metadata (md clob);

CREATE OR REPLACE PROCEDURE get_tables_md IS

-- 定义局部变量

h NUMBER; -- handle returned by 'OPEN'

th NUMBER; -- handle returned by 'ADD_TRANSFORM'

doc CLOB; -- metadata is returned in a CLOB

BEGIN

-- 指定对象类型

h := DBMS_METADATA."OPEN" ('TABLE');

-- 使用过滤器指定模式

DBMS_METADATA.SET_FILTER(h,'SCHEMA','sysdba') ;

-- 获取对象

LOOP

	doc := DBMS_METADATA.FETCH_CLOB(h);

   -- 当没有对象可获取时, FETCH_CLOB 返回 NULL

	EXIT WHEN doc IS NULL;

   -- 将元数据存入表中

	INSERT INTO my_metadata(md) VALUES (doc);

	COMMIT;

END LOOP;

-- 释放资源

DBMS_METADATA."CLOSE" (h);

END;

/

CALL get_tables_md;

SELECT * FROM my_metadata;

例5 使用parse items解析特定的元数据属性。

DROP TABLE my_metadata;

CREATE TABLE my_metadata (

   object_type VARCHAR2(30),

   name VARCHAR2(30),

   md CLOB);

CREATE OR REPLACE PROCEDURE get_tables_and_indexes IS

-- 定义局部变量

h1 NUMBER; -- 打开表返回的句柄

doc DBMS_METADATA.ku$_ddls; -- ku$_ddls,返回的元数据

ddl CLOB; -- 对象的ddl语句

pi DBMS_METADATA.ku$_parsed_items; --包含在ku$_ddL中的对象返回的解析项

objname VARCHAR2(30); -- 解析对象的名称

BEGIN

-- 指定对象类型: TABLE.

h1 := DBMS_METADATA."OPEN" ('TABLE');

-- 将表名作为解析项返回

DBMS_METADATA.SET_PARSE_ITEM(h1,'NAME');

-- 设置循环: 获取 TABLE 对象

LOOP

	doc := dbms_metadata.fetch_ddl(h1);

--当没有对象可获取时,FETCH_CLOB 返回 NULL

	EXIT WHEN doc IS NULL;

-- 循环ku$_ddls表中的行

	FOR i IN doc.FIRST..doc.LAST LOOP

  		ddl := doc(i).ddlText;

		pi := doc(i).parsedItem;

-- 循环返回的解析项

		IF pi IS NOT NULL AND pi.COUNT > 0 THEN

        	FOR j IN pi.FIRST..pi.LAST LOOP

            	IF pi(j).item='NAME' THEN

                	objname := pi(j).value;

                END IF;

        	END LOOP;

         END IF;

-- 将该对象的信息插入表 my_metadata中

    	 INSERT INTO my_metadata(object_type, name, md)

         	VALUES ('TABLE',objname,ddl);

        	COMMIT;

    	 END LOOP;

	END LOOP;

	DBMS_METADATA."CLOSE" (h1);

END;

/

CALL get_tables_and_indexes;

SELECT * FROM my_metadata;

例6 使用SET_TRANSFORM_PARAM格式化过滤约束。

--建表

DROP TABLE "SYSDBA"."T1";

CREATE TABLE "SYSDBA"."T1"

(

"C1" CHAR(10) NOT NULL,

"C2" CHAR(10),

CONSTRAINT "PK" PRIMARY KEY("C1")) STORAGE(ON "MAIN", CLUSTERBTR);

--格式化过滤约束

DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', FALSE, '');

--查看表定义

SELECT DBMS_METADATA.GET_DDL('TABLE','T1','SYSDBA');

执行结果:

CREATE TABLE "SYSDBA"."T1"

(

"C1" CHAR(10),

"C2" CHAR(10)) STORAGE(ON "MAIN", CLUSTERBTR);
微信扫码
分享文档
扫一扫
联系客服