oracle exec 从过程中收集表统计信息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14624650/
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
exec gather table stats from procedure
提问by Deniz
If I create a procedure:
如果我创建一个程序：
CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS IS
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS( 'SchName', 'TableName', CASCADE => TRUE);
END;
and execute it ;
并执行它；
EXEC SchameB.PRC_GATHER_STATS;
this gives me error ORA-20000: Unable to analyze TABLE "SchameA"."TableName", insufficient privileges or does not exist. But this works:
这给了我错误ORA-20000: Unable to analyze TABLE "SchameA"."TableName", insufficient privileges or does not exist。但这有效：
EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS( 'SchameA', 'TableName', CASCADE => TRUE);
The user who EXECs the procedure and the table are in different schemas.
谁的用户EXECS中的程序和表在不同的模式。
Why am I getting an error when doing this through a procedure?
为什么通过程序执行此操作时会出现错误？
回答by Alex Poole
To gather statistics on an object in another schema you need the ANALYZE ANYsystem privilege. I appears that the user that runs your procedure has that privilege, but granted through a role. As the documentation says:
要收集另一个模式中的对象的统计信息，您需要ANALYZE ANY系统权限。我似乎运行您的程序的用户具有该权限，但通过角色授予。正如文档所说：
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights.
在以定义者的权限执行的任何命名的 PL/SQL 块（存储过程、函数或触发器）中，所有角色都被禁用。
You can either GRANT ANALYZE ANYdirectly to your user, or create the procedure with invoker's rights, as:
您可以GRANT ANALYZE ANY直接联系您的用户，也可以创建具有调用者权限的过程，如下所示：
CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS
AUTHID CURRENT_USER IS
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;
/
When you EXECthe DBMS_STATSprocedure directly, it's running as an anonymous block, and those always run with invoker's rights - honouring roles.
当您直接执行EXEC该DBMS_STATS过程时，它作为匿名块运行，并且那些始终以调用者的权利运行 - 尊重角色。
回答by zlsmith86
If you want the procedure to be able to be ran by a user without the ANALYSE ANYrole then you can set the AUTHIDto be DEFINER
如果您希望该过程能够由没有ANALYSE ANY角色的用户运行，那么您可以AUTHID将DEFINER
CREATE OR REPLACE PROCEDURE SchameB.PRC_GATHER_STATS
AUTHID DEFINER IS
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS('SchName', 'TableName', CASCADE => TRUE);
END;
