oracle dba_jobs_running：尝试从过程访问时，表或视图不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/968857/
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
dba_jobs_running: table or view does not exist when trying to access from procedure
提问by PeterP
Simply querying running jobs using something like
使用类似的东西简单地查询正在运行的作业
select * from dba_jobs_running;
works fine when executed in my sqldevelopers SQL console.
在我的 sqldevelopers SQL 控制台中执行时工作正常。
However, it does not work, when having exactly the same statement within a procedure. Compilation fails with
但是，当在过程中具有完全相同的语句时，它不起作用。编译失败
PL/SQL: ORA-00942: table or view does not exist
Any ideas? Is there something like a scope to be considered?
有任何想法吗？是否有类似的范围需要考虑？
Any suggestions are highly appreciated, thanks in advance :)
任何建议都非常感谢，提前致谢:)
回答by cagcowboy
You probably need to do a direct GRANT of DBA_JOBS_RUNNING to the user that owns the procedure. Doing a GRANT via a role won't work.... the grant needs to be explicit.
您可能需要向拥有该过程的用户直接授予 DBA_JOBS_RUNNING 权限。通过角色进行 GRANT 是行不通的.... 授权必须是明确的。
EDIT:
编辑：
Doing a SELECT from within a procedure requires subtly different permissions to doing a SELECT from outside a procedure (e.g. in SQL-Developer). The user that owns a procedure must have been explicitly granted rights to the table or view... if running a query from outside a view this is not the case (you can be granted the permission through a role for example)
在过程中执行 SELECT 与从过程外部（例如在 SQL-Developer 中）执行 SELECT 所需的权限略有不同。拥有过程的用户必须已被明确授予对表或视图的权限...如果从视图外部运行查询，则情况并非如此（例如，您可以通过角色授予权限）
You need to connect as SYS and go:
您需要以 SYS 身份连接并执行以下操作：
GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO <user-that-owns-proc>;
回答by Vincent Malgrat
Procedures are executed without roles. One way to see if you can run a command in a procedure is to execute:
程序在没有角色的情况下执行。查看是否可以在过程中运行命令的一种方法是执行：
SQL> set role none;
Role set
You will have the same set of rights as your procedures:
您将拥有与您的程序相同的权利：
SQL> SELECT * FROM dba_jobs_running;
SELECT * FROM dba_jobs_running
ORA-00942: table or view does not exist
You have to grant select on the view directly to the user:
您必须将视图上的选择直接授予用户：
SQL> -- with dba account
SQL> grant select on dba_jobs_running to a;
Grant succeeded
You will then be able to compile the procedure:
然后，您将能够编译该过程：
SQL> -- with application schema
SQL> CREATE OR REPLACE PROCEDURE test_dba AS
2 BEGIN
3 FOR cc IN (SELECT * FROM dba_jobs_running) LOOP
4 NULL;
5 END LOOP;
6 END test_dba;
7 /
Procedure created
回答by Rob van Laarhoven
Is procedure owned by another user? If so have a look at: Definer and Invoker Rights for stored routines in PL/SQL manual.
过程是否由另一个用户拥有？如果是，请查看：PL/SQL 手册中存储例程的定义者和调用者权限。
Rob
抢
