dba_jobs_running:尝试从过程访问时，表或视图不存在 [英] dba_jobs_running: table or view does not exist when trying to access from procedure

问题描述

使用类似

select * from dba_jobs_running;

在我的 sqldevelopers SQL 控制台中执行时工作正常.

works fine when executed in my sqldevelopers SQL console.

但是，当在一个过程中有完全相同的语句时，它不起作用.编译失败

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 :)

推荐答案

您可能需要对拥有该过程的用户执行 DBA_JOBS_RUNNING 的直接 GRANT.通过角色进行 GRANT 是行不通的.... 授权需要是明确的.

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.

在过程中执行 SELECT 与从过程外部(例如在 SQL-Developer 中)执行 SELECT 所需的权限略有不同.拥有过程的用户必须已明确授予对表或视图的权限...如果从视图外部运行查询，则情况并非如此(例如，您可以通过角色授予权限)

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)

您需要以 SYS 身份连接并继续:

You need to connect as SYS and go:

GRANT SELECT ON SYS.DBA_JOBS_RUNNING TO <user-that-owns-proc>;

这篇关于dba_jobs_running:尝试从过程访问时，表或视图不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！