sql - dbms_metadata.get_ddl 不工作

Question

我想得到表CARD_TABLE的DDL在 XT模式

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它仍然得到了 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.

Answer 1

从 dbms_metadata documentation :

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 的对象定义。

即使有这个角色:

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，但如果您从一个过程调用它，则必须在过程声明中包含 an AUTHID clause ，并添加 AUTHID CURRENT_USER 。