REM---------------------------------------------------------------------------
REM-- Grant a user view privilege for the v$ tables
REM-- You must be connected as "sysdba" in order to run this script.
REM-- sysdba can only grant access to the v$ views.
REM-- From Tom Kyte
REM---------------------------------------------------------------------------
 
set echo on
spool db_grant_dynamic_views.log
whenever sqlerror exit 1
 
 
begin
    for x in ( select object_name
       from user_objects where object_type = 'VIEW'
       and object_name like 'V\_$%' escape '\' )   loop
 
       execute immediate 'grant select on ' || x.object_name || ' to &1';
 
    end loop;
end;
/
 
-- Show the grants that have been given
 
set pages 9999
column privilege  format a20
column table_name format a20
 
select
   table_name "Table Name",
   privilege "Privelege Granted"
from
   dba_tab_privs
where
   grantee = upper('&1');
 
exit;