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:
Because you are accessing a sys table by system in a stored procedure you need a direct grant on the underlying table.