logo - 跳到首页

VastbaseG100

基于openGauss内核开发的企业级关系型数据库。

Menu

DBMS_METADATA

功能描述

DBMS_METADATA包提供了一种从数据库字典中检索元数据作为XML或创建DDL并提交XML以重新创建对象的方法。该内置包包含以下模块：

子程序 描述
GET_DDL 获取创建对象的原数据的DDL语句。
GET_DEPENDENT_DDL 获取授权给某用户的物体的权限的DDL。
GET_GRANTED_DDL 获取用户的授权信息。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY='A'）。

子程序语法格式

GET_DDL

语法格式

DBMS_METADATA.GET_DDL (
object_type     IN VARCHAR2,
name            IN VARCHAR2,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

object_type支持如下类型：

  • table：表。

    • 不支持列存表，临时表。
    • 不包含指定storage、collate、row movment。
    • 列名限制仅支持not null与default number。
    • 仅支持分区表中分区列为数字类型。

  • index：索引。暂不支持显示索引为不可用的DDL。

  • constraint：主键。当前仅支持4种：primary key、 unique、 foreign 、 check

  • view：视图。

  • user：用户。

  • role：角色。

    不支持区分user与role。

  • tablespace：表空间。

  • materialized_view：物化视图。

    不支持增量物化视图。

  • dblink：远程连接定义。

  • trigger：触发器。

  • sequence：序列。

    不支持large sequence，不支持owned。

  • function：函数。

  • package：包。

  • procedure：存储过程。

  • package_body：包体。

示例

1、创建表空间。

create tablespace space_1103409 relative location 'tablespace/tablespace_1';

2、创建表。

create table tab1_1103409(id int,c1 char(8)) tablespace space_1103409;

3、调用get_ddl。

select DBMS_METADATA.GET_DDL('TABLE','tab1_1103409','public');

返回结果为：

                         get_ddl
-----------------------------------------------------------------------------------------
 create table "public"."tab1_1103409" ("id" int4, "c1" bpchar(8)) with(orientation=row,compression=no,fillfactor=80) tables
pace "space_1103409"
(1 row)

GET_DEPENDENT_DDL

语法格式

DBMS_METADATA.GET_DEPENDENT_DDL (
object_type         IN VARCHAR2,
base_object_name    IN VARCHAR2,
base_object_schema  IN VARCHAR2 DEFAULT NULL,
version             IN VARCHAR2 DEFAULT 'COMPATIBLE',
model               IN VARCHAR2 DEFAULT 'ORACLE',
transform           IN VARCHAR2 DEFAULT 'DDL',
object_count        IN NUMBER   DEFAULT 10000)
RETURN CLOB;

object_type支持如下类型：

  • object_grant：检查赋权情况。

    当前版本支持对table和sequence赋权情况的检查。

  • index：索引。仅限用于表类型。

  • constraint：主键。仅限用于表类型。

示例

1、创建表。

create table tab_1103856(c1 int,c2 int);

2、创建用户。

create user use1_1103856 password 'Aa@123456';
create user use2_1103856 password 'Aa@123456';
create user use3_1103856 password 'Aa@123456';

3、将表的权限赋给public。

grant select,insert,update,delete on table tab_1103856 to public;

4、将表的权限赋予用户。

grant select,insert,update,delete on table tab_1103856 to use1_1103856;
grant truncate,alter,drop,comment,index,vacuum on tab_1103856 to use2_1103856;
grant all on tab_1103856 to use3_1103856 with grant option;

5、调用函数。

select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','tab_1103856','public');

返回结果为：

                                  get_dependent_ddl
--------------------------------------------------------------------------------------
 grant insert on table "public"."tab_1103856" to "public"                            +
 grant select on table "public"."tab_1103856" to "public"                            +
 grant update on table "public"."tab_1103856" to "public"                            +
 grant delete on table "public"."tab_1103856" to "public"                            +
 grant insert on table "public"."tab_1103856" to "use1_1103856"                      +
 grant select on table "public"."tab_1103856" to "use1_1103856"                      +
 grant update on table "public"."tab_1103856" to "use1_1103856"                      +
 grant delete on table "public"."tab_1103856" to "use1_1103856"                      +
 grant truncate on table "public"."tab_1103856" to "use2_1103856"                    +
 grant alter on table "public"."tab_1103856" to "use2_1103856"                       +
 grant drop on table "public"."tab_1103856" to "use2_1103856"                        +
 grant comment on table "public"."tab_1103856" to "use2_1103856"                     +
 grant index on table "public"."tab_1103856" to "use2_1103856"                       +
 grant vacuum on table "public"."tab_1103856" to "use2_1103856"                      +
 grant insert on table "public"."tab_1103856" to "use3_1103856" with grant option    +
 grant select on table "public"."tab_1103856" to "use3_1103856" with grant option    +
 grant update on table "public"."tab_1103856" to "use3_1103856" with grant option    +
 grant delete on table "public"."tab_1103856" to "use3_1103856" with grant option    +
 grant truncate on table "public"."tab_1103856" to "use3_1103856" with grant option  +
 grant references on table "public"."tab_1103856" to "use3_1103856" with grant option+
 grant trigger on table "public"."tab_1103856" to "use3_1103856" with grant option   +
 grant alter on table "public"."tab_1103856" to "use3_1103856" with grant option     +
 grant drop on table "public"."tab_1103856" to "use3_1103856" with grant option      +
 grant comment on table "public"."tab_1103856" to "use3_1103856" with grant option   +
 grant index on table "public"."tab_1103856" to "use3_1103856" with grant option     +
 grant vacuum on table "public"."tab_1103856" to "use3_1103856" with grant option
(1 row)

GET_GRANTED_DDL

语法格式

DBMS_METADATA.GET_GRANTED_DDL (
object_type     IN VARCHAR2,
grantee         IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL',
object_count    IN NUMBER   DEFAULT 10000)
RETURN CLOB;

object_type支持如下类型：

  • system_grant：系统权限授予。

  • object_grant：对象授予。

    当前版本object_grant仅支持对SEQUENCE、FUNCTION、PROCEDURE、TABLESPACE、DATABASE五种对象类型 进行赋权情况的检查。

  • role_grant：角色授予。

示例

1、创建用户。

create user use_1103894 password 'Aa@123456';
create user use1_1103894 password 'Aa@123456';  
create user use2_1103894 password 'Aa@123456';

2、创建数据库。

create database db_1103894;

3、赋权用户权限。

grant create on database db_1103894 to use_1103894;
grant alter on database db_1103894 to use_1103894;
grant drop on database db_1103894 to use_1103894;
grant connect on database db_1103894 to use_1103894;
grant temporary on database db_1103894 to use_1103894;
grant temp on database db_1103894 to use_1103894;
grant comment on database db_1103894 to use_1103894;
grant drop on database db_1103894 to use1_1103894;
grant connect on database db_1103894 to use1_1103894;
grant temporary on database db_1103894 to use1_1103894;
grant temp on database db_1103894 to use1_1103894;
grant comment on database db_1103894 to use1_1103894;  
grant all on database db_1103894 to use2_1103894 with grant option;

4、调用函数（use_1103894）。

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','use_1103894') from dual;

返回结果为：

                   get_granted_ddl
-----------------------------------------------------
 grant all on database "db_1103894" to "use_1103894"
(1 row)

5、调用函数（use1_1103894）。

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','use1_1103894') from dual;

返回结果为：

                     get_granted_ddl
----------------------------------------------------------
 grant drop on database "db_1103894" to "use1_1103894"   +
 grant comment on database "db_1103894" to "use1_1103894"+
 grant temp on database "db_1103894" to "use1_1103894"   +
 grant connect on database "db_1103894" to "use1_1103894"
(1 row)

6、调用函数（use2_1103894）。

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','use2_1103894') from dual;

返回结果为：

                            get_granted_ddl
------------------------------------------------------------------------
 grant all on database "db_1103894" to "use2_1103894" with grant option
(1 row)

参数说明

object_type 对象类型。
name 对象名称。
schema 对象架构，默认为 NULL。
version 要提取的元数据版本，默认为'COMPATIBLE'（此参数无实际效果）。
model 要使用的对象模型，默认为'ORACLE'（此参数无实际效果）。
transfom 输出上的转换名称，GET*XML 默认为 NULL，GET*DDL默认为DDL，GET*SXML 默认为 SXML(如果在 GET_DDL中指定为NULL，则会输出XML 格式，同样，在 GET_XML 中指定为DDL，也会输出为 sql语句格式（此参数无实际效果）。
base_object_name 基础对象名称。
base_object_schema 基础对象架构。
grantee 授予的用户，默认为当前用户。
object_count 要返回的最大对象数默认 10000（此参数无实际效果，object_count 无限制）。

示例

在存储过程中调用内置包。

1、创建表。

create table tab_1103905(id int,c1 char(8));

2、创建用户。

create user use_1103905 password 'Aa@123456';

3、将表的权限赋予用户。

grant insert,update on table tab_1103905 to use_1103905;

4、创建存储过程。

create or replace procedure pro_1103905(n int)
as
c1 record;
c2 record;
c3 record;
begin
if n>5 then
select DBMS_METADATA.GET_DDL('TABLE','tab_1103905','public') into c1;
select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','tab_1103905','public') into c2;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','use_1103905') into c3;
raise notice '%',c1;
raise notice '%',c2;
raise notice '%',c3;
else
raise notice 'No action!';
end if;
end;
/

5、调用存储过程，入参为6。

call pro_1103905(6);

返回结果为：

NOTICE:  ("create table ""public"".""tab_1103905"" (""id"" int4, ""c1"" bpchar(8)) with(orientation=row,compression=no,fillfactor=80) ")
NOTICE:  ("grant insert on table ""public"".""tab_1103905"" to ""use_1103905""
grant update on table ""public"".""tab_1103905"" to ""use_1103905""")
NOTICE:  ("grant insert on table ""public"".""tab_1103905"" to ""use_1103905""
grant update on table ""public"".""tab_1103905"" to ""use_1103905""")
 pro_1103905
-------------

(1 row)

6、调用存储过程，入参为3。

call pro_1103905(3);

返回结果为：

NOTICE:  No action!
 pro_1103905
-------------

(1 row)