Accessing V$sess_io and v$sqltext
Date: 1995/07/11
Message-ID: <3tuq35$r2l_at_dub-news-svc-2.compuserve.com>#1/1
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