DBMS_METADATA系统包

📄字数 2.1K

👁️阅读量 加载中...

一、概述

DBMS_METADATA系统包是一个元数据提取工具包，用于获取数据库对象的DDL（数据定义语言）语句。

在该系统包中提供了以下接口：

包体函数名	简要描述
GET_DDL	用于获取对象的DDL语句
GET_DDL_INDEX	用于获取指定表中所有索引对象的DDL语句

典型应用场景，如：

• 导出建表语句
• 获取表上所有索引的创建语句，用于索引结构迁移
• 结合系统表，如SYS_TABLES遍历对象，批量导出DDL

二、GET_DDL

2.1 功能描述

该方法有2种重载方式，支持不同参数获取对象的DLL（数据定义语言）语句。当前暂只支持获取表对象、索引对象的DDL语句

2.2 方法声明

• 重载形式一：指定对象名，获取对象的DLL语句

  FUNCTION GET_DDL(
      OBJ_NAME IN VARCHAR
      ) RETURN CLOB;

• 重载形式二：指定对象类型、对象名以及模式名，获取对象的DLL语句

  FUNCTION GET_DDL(
      OBJ_TYPE    IN VARCHAR, 
      OBJ_NAME    IN VARCHAR, 
      SCHEMA_NAME IN VARCHAR DEFAULT NULL
      ) RETURN CLOB;

2.3 参数说明

参数名	类型	取值范围	是否可选	描述
OBJ_TYPE	VARCHAR	无	否	对象类型
OBJ_NAME	VARCHAR	无	否	对象名
SCHEMA_NAME	VARCHAR	无	是，默认为NULL	模式名

2.4 示例

-- 创建一张表及索引
SQL> CREATE TABLE tbr_metadata(id INT, name VARCHAR);
SQL> CREATE INDEX idx ON tbr_metadata(name) INDEXTYPE IS BTREE;

-- 指定表名，获取表对象tbr_metadata的DDL语句
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL('tbr_metadata'));

+----------------------------------------+
|                 EXPR1                  |
+----------------------------------------+
| CREATE TABLE "SYSDBA"."TBR_METADATA" (+|
| "ID" INTEGER,                         +|
| "NAME" VARCHAR                        +|
| )                                     +|
| PCTFREE 15                            +|
| COPY NUMBER 3                         +|
| ;                                     +|
|                                        |
+----------------------------------------+


-- 指定表对象、表名，获取表对象tbr_metadata的DDL语句
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL('TABLE', 'tbr_metadata'));

+----------------------------------------+
|                 EXPR1                  |
+----------------------------------------+
| CREATE TABLE "SYSDBA"."TBR_METADATA" (+|
| "ID" INTEGER,                         +|
| "NAME" VARCHAR                        +|
| )                                     +|
| PCTFREE 15                            +|
| COPY NUMBER 3                         +|
| ;                                     +|
|                                        |
+----------------------------------------+

-- 指定表对象、表名以及模式名，获取表对象tbr_metadata的DDL语句
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL('TABLE', 'tbr_metadata', 'sysdba'));

+----------------------------------------+
|                 EXPR1                  |
+----------------------------------------+
| CREATE TABLE "SYSDBA"."TBR_METADATA" (+|
| "ID" INTEGER,                         +|
| "NAME" VARCHAR                        +|
| )                                     +|
| PCTFREE 15                            +|
| COPY NUMBER 3                         +|
| ;                                     +|
|                                        |
+----------------------------------------+

-- 指定索引对象、表名，获取表tbr_metadata上的所有索引对象的DDL语句
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL('INDEX', 'tbr_metadata'));

+----------------------------------------------------------------------------------+
|                                      EXPR1                                       |
+----------------------------------------------------------------------------------+
| CREATE INDEX "IDX" ON "SYSDBA"."TBR_METADATA"("NAME") INDEXTYPE IS BTREE GLOBAL;+|
|                                                                                  |
+----------------------------------------------------------------------------------+

-- 指定索引对象、表名、模式名，获取表tbr_metadata上的所有索引对象的DDL语句
SQL> SELECT  UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL('INDEX', 'tbr_metadata', 'sysdba'));

+----------------------------------------------------------------------------------+
|                                      EXPR1                                       |
+----------------------------------------------------------------------------------+
| CREATE INDEX "IDX" ON "SYSDBA"."TBR_METADATA"("NAME") INDEXTYPE IS BTREE GLOBAL;+|
|                                                                                  |
+----------------------------------------------------------------------------------+

2.5 场景应用

• 场景一：快速查看表、索引等对象的定义，检查字段变更、索引策略等是否符合规范
• 场景二：可用于定期导出对象DDL作为备份，保证元数据可重建

三、GET_DDL_INDEX

3.1 功能描述

该接口用于获取指定表上所有索引对象的DLL语句

3.2 方法声明

FUNCTION GET_DDL_INDEX(
    TAB_NAME IN VARCHAR, 
    IDX_NAME IN VARCHAR DEFAULT 'ALL'
    ) RETURN CLOB;

3.3 参数说明

参数名	类型	取值范围	是否可选	描述
TAB_NAME	VARCHAR	无	否	表名
IDX_NAME	VARCHAR	无	是，默认值为ALL	索引名（当前暂不支持指定索引名）

3.4 示例

-- 获取tbr_1表中所有索引对象的DDL语句
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL_INDEX('tbr_metadata'));

+----------------------------------------------------------------------------------+
|                                      EXPR1                                       |
+----------------------------------------------------------------------------------+
| CREATE INDEX "IDX" ON "SYSDBA"."TBR_METADATA"("NAME") INDEXTYPE IS BTREE GLOBAL;+|
|                                                                                  |
+----------------------------------------------------------------------------------+

-- 获取tbr_1表中所有索引对象的DDL语句
SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(DBMS_METADATA.GET_DDL_INDEX('tbr_metadata','all'));

+----------------------------------------------------------------------------------+
|                                      EXPR1                                       |
+----------------------------------------------------------------------------------+
| CREATE INDEX "IDX" ON "SYSDBA"."TBR_METADATA"("NAME") INDEXTYPE IS BTREE GLOBAL;+|
|                                                                                  |
+----------------------------------------------------------------------------------+

3.5 场景应用

• 场景一：查看表上索引定义，确认是否存在重复索引、冗余索引等问题
• 场景二：备份索引结构，以便需要的时候重建完整环境