oracle dbms_metadata.get_ddl 不工作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26026068/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
dbms_metadata.get_ddl not working
提问by user2711819
I want to get the DDL of Table CARD_TABLEin XTschema
我想CARD_TABLE在XT模式中 获取表的 DDL
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
But my select Query works
但我的选择查询有效
select count(*) from XT.CARD_TABLE;
count(*)
---------
0
I queried dba_objectsit still got the table:
我查询dba_objects它仍然得到表：
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.
回答by Alex Poole
From the dbms_metadatadocumentation:
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.
如果非特权用户被授予对其他人模式中对象的某种形式的访问权限，他们将能够通过元数据 API 检索授权规范，但不能检索对象的实际元数据。
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_ROLErole granted to your user to be able to get XT's object definition.
因此，除非您以特权用户身份连接，否则您无法看到其他用户对象的 DDL。您需要以 身份连接SYS，或者将SELECT_CATALOG_ROLE角色授予您的用户才能获得 XT 的对象定义。
Even with that 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.
在存储过程、函数和定义者权限包中，角色（例如 SELECT_CATALOG_ROLE）被禁用。因此，这样的 PL/SQL 程序只能为自己模式中的对象获取元数据。如果您想编写一个 PL/SQL 程序来获取不同模式中对象的元数据（基于调用者对 SELECT_CATALOG_ROLE 的拥有），您必须使程序具有调用者权限。
If you're calling dbms_metadatafrom an anonymous PL/SQL block that doesn't matter, but if you're calling it from a procedure you will have to include an AUTHIDclausein the procedure declaration, adding AUTHID CURRENT_USER.
如果您dbms_metadata从一个无关紧要的匿名 PL/SQL 块调用，但如果您从一个过程调用它，则必须在过程声明中包含一个AUTHID子句，添加AUTHID CURRENT_USER.
