ORA-00942: table or view does not exist

When calling the dynamic performance view related to Oracle in a process, package, function, or trigger, you must grant appropriate permissions. Otherwise, you will receive an error message indicating that the table and view do not exist. Even if you can query these views separately. Because the dynamic performance view depends on the underlying table, you cannot directly grant permissions to it. The following is an example of this phenomenon.

 

1. Examples of failed compilation of process call Dynamic View

SQL> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.5.0 - 64bit ProductionSQL> show user;USER is "GX_ADM"SQL> CREATE OR REPLACE PROCEDURE tst  2  AS  3      v_usr   VARCHAR2(30);  4  BEGIN  5  SELECT username INTO v_usr FROM v$process WHERE ROWNUM < 2;  6  DBMS_OUTPUT.put_line ('Username is ' || v_usr);  7  END;  8  /Warning: Procedure created with compilation errors.SQL> show errors;Errors for PROCEDURE TST:LINE/COL ERROR-------- -----------------------------------------------------------------5/1      PL/SQL: SQL Statement ignored5/33     PL/SQL: ORA-00942: table or view does not exist

2. Analysis and Solution

-- According to the error message shown above, the table and view do not exist. In fact, the dynamic performance view is a synonym and is not a real view, next I tried to use the sys account to grant it permissions to the desired user -- A ORA-02030 error message was received, that is, only a fixed table and view can be granted permissions SQL> conn/as sysdbaConnected. SQL> grant select on v $ process to gx_adm; grant select on v $ process to gx_adm * ERROR at line 1: ORA-02030: can only select from fixed tables/viewsSQL> conn gx_adm/xxx --> use the gx_adm user to connect to the Database Error accessing PRODUCT_USER_PROFILEWarning: Product user profile informa Tion not loaded! You may need to run PUPBLD. SQL as SYSTEMConnected. -- the user can access the dynamic performance view of SQL> select username FROM v $ process WHERE ROWNUM <2 and username is not null; USERNAME --------------- oracleSQL> conn/as sysdbaConnected. -- Author: Leshami -- Blog: http://blog.csdn.net/leshami--based on a true visual image granting permission SQL> grant select on v _ $ process to gx_adm; Grant succeeded. -- compile gx_adm @ CNMMBO> alter procedure tst compile; Procedure altered again. -- We can also view the underlying access object as X $ KSUPR through the execution plan, which is why the previous authorization failed. SQL> set autot trace exp; SQL> select username FROM v $ process WHERE ROWNUM <2 and username is not null; Execution Plan statement | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | ------------------------------------------------------------------ | 0 | select statement | 1 | 35 | 0 (0) | 1 | count stopkey | 2 | fixed table full | X $ KSUPR | 1 | 35 | 0 (0) | ------------------------------------------------------------------

3. Metalink article (Doc ID 1062335.6)
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
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.

 

More references

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in oracle rac (listener. ora tnsnames. ora)
Oracle rac listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle LISTENER password (LISTENER)
Configure the ORACLE client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
SYSTEM tablespace management and Backup Recovery
SYSAUX tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the ORACLE architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle ONLINE redo LOG FILE)
Oracle Control File)
Oracle archiving logs
Oracle ROLLBACK and UNDO)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)