在package中查询V$表报ORA-00942

ORA-942 when select from any v$view within stored PL/SQL procedure (Doc ID 1062335.6)


Problem Description: 
~~~~~~~~~~~~~~~~~~~~
You are selecting from a system view, such as V$SESSION, from within a PL/SQL
stored procedure and you receive an ORA-00942 error. 

    ORA-00942: table or view does not exist
        Cause: The table or view entered does not exist, a synonym
               that is not allowed here was used, or a view was
               referenced where a table is required.  Existing user
               tables and views can be listed by querying the data
               dictionary.  Certain privileges may be required to
               access the table.  If an application returned this
               message, the table the application tried to access
               does not exist in the database, or the application
               does not have access to it.
       Action: Check each of the following:
               - the spelling of the table or view name.
               - that a view is not specified where a table is
                 required. 
               - that an existing table or view name exists.  Contact
                 the database administrator if the table needs to be
                 created or if user or application privileges are
                 required to access the table.
               Also, if attempting to access a table or view in another
               schema, make certain the correct schema is referenced
               and that access to the object is granted.


Problem Explanation: 
~~~~~~~~~~~~~~~~~~~~
The ORA-00942 is produced because the privilege to use the V$ views has been
granted to the user via a role, roles are not in effect within stored PL/SQL procedures.


Problem References: 
~~~~~~~~~~~~~~~~~~~ 
Oracle7 Server Application Developer's Guide

 
Search Words: 
~~~~~~~~~~~~~
ORA-942



Solution Description: 
~~~~~~~~~~~~~~~~~~~~~

Grant the owner of the stored procedure select directly on the needed V$ view.
(Remember that the grant must be made on the actual table or view name, not the synonym):

SQL> GRANT SELECT on V_$SESSION to <user_name>;


Solution Explanation: 
~~~~~~~~~~~~~~~~~~~~~ 
Granting the owner of the PL/SQL stored procedure select directly on the required 
V$ view will allow the select to complete successfully.

------------------

我的实际问题是在package中查询实例名

select instance_name from v$instance;

报ORA-00942

按照以上文档，以DBA登陆sql

[oracle@erp dbhome_1]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Sep 17 10:58:23 2021

Connected to:
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production

SQL> GRANT SELECT on V_$INSTANCE to dsdata;

Grant succeeded.

SQL>

再取执行package程序包，程序包编译通过

这里实际查询的视图为v$instance，在grant时需要修改为V_$INSTANCE 即可。