ORA-00942: tableorviewdoesnotexist.

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-- Grant permissions to 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-00942: table or view does not exist

Solution Description:

Grant the owner of the stored procedure select directly on the needed V $ view.

SQL> GRANT SELECT on V _ $ SESSION to <user_name>;

More references

For more information about Oracle RAC, see

For more information about the basics and concepts of Oracle network configuration, see:

For more information about user-managed backup and recovery, see

For information on RMAN backup recovery and management, see

For the ORACLE architecture, see