ORA-02030: can only select from fixed tables/views

ORA-02030: can only select from fixed tables/views
Cause: An attempt is being made to perform an operation other than a retrieval from a fixed table/view.
Action: You may only select rows from fixed tables/views.

The request came from a team member that he wanted execute following SQL on V$SQLTEXT, V$SESSION AND V$PROCESS, from a "READONLYUSER" user, to look at the currently Active SQLs on the database.

readonlyuser@xe> select
  2    s.username,
  3    s.machine,
  4    s.module,
  5    spid,
  6    s.LAST_CALL_ET Duration,
  7    substr(s.sid || ',' || s.serial#,0,15) sid,
  8    sql_text
  9  from
 10    v$sqltext,
 11    v$session s,
 12    v$process p
 13  where
 14    address=sql_address
 15    and hash_value=sql_hash_value
 16    and paddr=addr
 17    and status='ACTIVE'
 18  order by s.sid,piece;
  v$process p
  *
ERROR at line 12:
ORA-00942: table or view does not exist

This seems to be an access issue, so team tried to grant SELECT on V$SQLTEXT, V$SESSION AND V$PROCESS to his user from SYS, but it got failed with ORA-02030 exception.

sys@ngarg> grant select on V$SESSION to READONLYUSER;
grant select on V$SESSION to READONLYUSER
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

As ORA-02030 says, that we can only select from fixed tables/views, which means V$SESSION is not a table or view but is of some other object type?

We first checked the object type of V$SQLTEXT, V$SESSION AND V$PROCESS.

sys@ngarg> select object_name, object_type from dba_objects
  2  where object_name in ('V$SQLTEXT', 'V$SESSION', 'V$PROCESS');

OBJECT_NAME               OBJECT_TYPE
------------------------  ------------
V$PROCESS                 SYNONYM
V$SESSION                 SYNONYM
V$SQLTEXT                 SYNONYM

As we can see here that V$SQLTEXT, V$SESSION AND V$PROCESS are actually synonyms, We need to find out to which tables or views they are pointing to -

sys@ngarg> select table_owner, table_name from dba_synonyms
  2  where synonym_name in ('V$SQLTEXT', 'V$SESSION', 'V$PROCESS');

TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------
SYS                            V_$PROCESS
SYS                            V_$SESSION
SYS                            V_$SQLTEXT

Now we have base tables/views names, let us try to grant select on base tables again.

sys@ngarg> grant select on V_$PROCESS to READONLYUSER;
Grant succeeded.

sys@ngarg> grant select on V_$SQLTEXT to READONLYUSER;
Grant succeeded.

sys@ngarg> grant select on V_$SESSION to READONLYUSER;
Grant succeeded.

Great! The grant was given to READONLYUSER, and the person was able to execute the SQL from READONLYUSER.

Before ending the post, I wanted to add - Oracle Database maintains a set of virtual tables that record current database activity. These views are dynamic because they are continuously updated while a database is open and in use. The views are sometimes called V$ views because their names begin with V$.


Related Posts:
- Query optimization tips for Oracle
- DBMS_PROFILER: Overview and How to Install
- Oracle : TKPROF simple steps
- Oracle SQL Developer - Autotrace - Insufficient Privileges
- Steps to debug SQL Performance issue in Oracle Database