Accessing V$sess_io and v$sqltext

 jwilkin_at_max.wosc.osshe.edu writes:
>I need to be able to select from the v$ tables from a user other than sys.
>For most of them, this is not a problem. Select access has been granted to
>the proper user. But when I try to grant select access to v$sess_io or
>v$sqltext, I get ORA-02030: can only select from fixed tables/views.
>
>Is there any way to access these tables from another user? I really don't want
>to use sys!
>
>etc.

V$ views, essentially for monitoring runtime statistics/parameters are available by default only to SYS.

At least since Ver 7.1.3 (may be earlier, someone may correct me on this) there is available a simple script:

    $ORACLE_HOME/rdbms/admin/utlmontr.sql for UNIX

    (but location may vary acc. to platform)

which creates for you a role called MONITORER and then provides access to all V$ views to this role. However note that the script must be run from as SYS or INTERNAL from sqldba. There is a detailed enough comment at the top explaining it all along with any prerequisites. Having run the script, whenever you wish to allow a user access these views, grant the role MONITORER to the user.

Check it out !!! The $ORACLE_HOME/rdbms/admin directory has a lot of other useful stuff, too.

Cheers

Rajiv Tandon Received on Tue Jul 11 1995 - 00:00:00 CEST