欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

ORA-02030问题解决方法又一例

程序员文章站 2022-04-30 14:57:28
...

给普通用户赋予对v$视图的查询权限,结果报 ORA-02030 错误,ORA-02030: 只能从固定的表/视图查询(ORA-02030: can only select f

ORA-02030问题解决方法又一例

首页 → 数据库技术

背景:

阅读新闻

ORA-02030问题解决方法又一例

[日期:2014-06-21] 来源:Linux社区 作者:Criss [字体:]

给普通用户赋予对v$视图的查询权限,结果报 ORA-02030 错误
ORA-02030: 只能从固定的表/视图查询(ORA-02030: can only select from fixed tables/views)

问题背景:
在一个dba用户下的存储过程中需要使用v$的视图(v$client_stats)

使用命令行测试:
SQL> variable total number;
SQL> begin
2 select count(1) into :total from v$client_stats;
3 dbms_output.put_line('total = '|| :total);
4 end;
5 /

PL/SQL procedure successfully completed
total
---------
0

这样并没有问题,,于是编辑成过程执行

使用过程
SQL> create or replace procedure p_test
2 authid current_user
3 as
4 v_all number := 9999;
5 begin
6 select count(1)
7 into v_all from v$client_stats;
8
9 dbms_output.put_line('v_all =' ||v_all );
10 end;
11 /

Warning: Procedure created with compilation errors

具体错位信息
Compilation errors for PROCEDURE ZHANGBIN.P_TEST

Error: PL/SQL: ORA-00942: 表或视图不存在
Line: 9
Text: FROM v$client_stats;

Error: PL/SQL: SQL Statement ignored
Line: 7
Text: SELECT COUNT(1)

此时需要给用户赋查询权限(于是产生了ORA-02030错误)

SQL> show user;
User is "SYS"

SQL> grant select on v$client_stats to zhangbin;

grant select on v$client_stats to zhangbin

ORA-02030: 只能从固定的表/视图查询

解决方法:

SQL> select * from dba_synonyms t where t.synonym_name = 'V$CLIENT_STATS';

OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------
PUBLIC V$CLIENT_STATS SYS V_$CLIENT_STATS

SQL> grant select on V_$CLIENT_STATS to zhangbin;

Grant succeeded

切换到普通用户
SQL> show user;
User is "zhangbin"

SQL> create or replace procedure p_test
2 as
3 v_all number :=9999;
4 begin
5 select count(1)
6 into v_all
7 from v$client_stats;
8 dbms_output.put_line('v_all =' ||v_all );
9 end;
10 /

Procedure created

SQL> set serveroutput on
SQL> exec p_test;

v_all =0

PL/SQL procedure successfully completed

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2