Another example of ORA-02030 Solution

Another example of ORA-02030 Solution

Give normal users the query permission on the v $ view and result in a ORA-02030 Error
ORA-02030: only queries from fixed tables/views (ORA-02030: can only select from fixed tables/views)
 
Problem Background:
You need to use the v $ view (v $ client_stats) in the stored procedure of a dba user)
 
Use the command line to test:
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
 
So there is no problem, so it is edited into a process for execution.
 
Procedure
SQL> create or replace procedure p_test
2 authid current_user
3
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
 
Specific misplacement Information
Compilation errors for procedure zhangbin. P_TEST
 
Error: PL/SQL: ORA-00942: The table or view does not exist
Line: 9
Text: FROM v $ client_stats;
 

Error: PL/SQL: SQL Statement ignored
Line: 7
Text: select count (1)
 
At this point, you need to assign the query permission to the user (so a ORA-02030 error is generated)
 
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: only queries from a fixed table/View
 
Solution:
 
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
 
Switch to normal user
SQL> show user;
User is "zhangbin"
 
SQL> create or replace procedure p_test
2
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

Installing Oracle 12C in Linux-6-64

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian