ORA-00942 when trying to select from gv$session inside of procedure

oracleplsql

I have been tasked with allowing developers to kill their own sessions in the Development database. I've written a procedure, but for some reason I'm getting PL/SQL: ORA-00942: table or view does not exist when trying to call gv$session. Here's the proc, being compiled as SYSTEM. Running on an 11.2.0.4.3, 2 node RAC database:

create or replace procedure kill_dev_session (v_osname in varchar2)
is
  lv_env     VARCHAR2(30 char);
  li_inst_id number;
  li_sid     number;
  li_serial  number;
begin
  select replace(global_name, '.DOMAIN.COM', '') 
    into lv_env
    from global_name;

  select inst_id, sid, serial#
    into li_inst_id, li_sid, li_serial
    from sys.gv$session
   where upper(osuser) = upper(v_osname)
  group by inst_id, sid, serial#;

  EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION'||''||li_sid||','||li_serial||','||li_inst_id||'''';

  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';
  UTL_MAIL.send(sender => lv_env||'@db01.domain.com',
              recipients => 'dba@domain.com',
              subject => 'Session killed by '||v_osname||' user in '||lv_env,
              message => 'This user killed a session using the kill_dev_session procedure.',
            mime_type => 'text; charset=us-ascii');

end;
/

So here's the rub: every single developer logs in with the same user, so I can't really use username. I don't want to open up blanket alter system for every single user. This would allow a dev manager to pass in a username (i.e. jsmith) and kill that session, and would email us DBAs that this happened, so we can see if the devs are doing this a lot.

Any thoughts as to why I'm seeing this:
Errors for PROCEDURE KILL_DEV_SESSION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
12/3     PL/SQL: SQL Statement ignored
14/10    PL/SQL: ORA-00942: table or view does not exist

Thanks in advance!

Best Answer

Try doing this:

GRANT SELECT ON gv_$session TO system;

Because you are accessing a sys table by system in a stored procedure you need a direct grant on the underlying table.