dbms_metadata.get_ddl无法正常工作 [英] dbms_metadata.get_ddl not working

问题描述

我想获取XT模式中表CARD_TABLE的DDL

I want to get the DDL of Table CARD_TABLE in XT schema

SQL> select dbms_metadata.get_ddl('TABLE','CARD_TABLE','XT') from dual;
    ERROR:
    ORA-31603: object "CARD_TABLE" of type TABLE not found in
    schema "XT"
    ORA-06512: at "SYS.DBMS_METADATA", line 5746
    ORA-06512: at "SYS.DBMS_METADATA", line 8333
    ORA-06512: at line 1

但是我选择的查询有效

select count(*) from XT.CARD_TABLE;
count(*)
---------
0

我查询了dba_objects它是否仍然有该表:

I queried dba_objects it still got the table:

    SQL> select owner,object_type from DBA_OBJECTS
    where object_name='CARD_TABLE'  2
      3  ;


PUBLIC     SYNONYM
    XT     TABLE PARTITION
    XT     TABLE PARTITION
    XT     TABLE PARTITION
    XT     TABLE
    XT     TABLE PARTITION
    VAT    TABLE

    7 rows selected.

推荐答案

来自

如果授予非特权用户某种方式的访问权，则他们将能够通过Metadata API而不是对象的实际元数据来检索授予规范.

If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

因此，除非您以特权用户身份连接，否则您将看不到其他用户对象的DDL.您需要以SYS身份进行连接，或者将SELECT_CATALOG_ROLE角色授予用户才能获取XT的对象定义.

So unless you're connected as a privileged user, you can't see the DDL for another user's objects. You would need to connect as SYS, or have the SELECT_CATALOG_ROLE role granted to your user to be able to get XT's object definition.

即使扮演该角色:

在存储过程，函数和定义者权限包中，角色(例如SELECT_CATALOG_ROLE)被禁用.因此，这样的PL/SQL程序只能在其自己的架构中获取对象的元数据.如果要编写一个PL/SQL程序，以不同模式(基于调用者对SELECT_CATALOG_ROLE的拥有)来获取对象元数据，则必须使程序具有调用者权限.

In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

如果从无关紧要的匿名PL/SQL块中调用dbms_metadata，但是如果从过程中调用dbms_metadata，则必须包含

If you're calling dbms_metadata from an anonymous PL/SQL block that doesn't matter, but if you're calling it from a procedure you will have to include an AUTHID clause in the procedure declaration, adding AUTHID CURRENT_USER.

这篇关于dbms_metadata.get_ddl无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！