1

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!

  • Try running this query and returning the results. – Gandolf989 Dec 12 '14 at 16:19
1

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.

  • Damn, is it that simple? I'll check this out, thanks Gandolf. – captjackvane Dec 12 '14 at 15:44
  • Didn't work:11:00:26 sys@DEV> grant select on gv_$session to system;` Grant succeeded. – captjackvane Dec 12 '14 at 16:03
  • Grr. Didn't work: 11:00:26 sys@DEV> grant select on gv_$session to system; Grant succeeded. 11:00:47 system@DEV> alter procedure kill_dev_session compile; Warning: Procedure altered with compilation errors. Same error. Sorry for the formatting in this comment. – captjackvane Dec 12 '14 at 16:10
  • Try running this query and returning the results: SET LINESIZE 100 COL text FORMAT A80 WORD WRAP SELECT line, text FROM dba_errors WHERE owner = 'SYSTEM' AND name = 'KILL_DEV_SESSION' ORDER BY 1; – Gandolf989 Dec 12 '14 at 16:20
  • 11:26:23 system@DEV> SELECT line, text FROM dba_errors WHERE owner = 'SYSTEM' AND name = 'KILL_DEV_SESSION' ORDER BY 1; LINE TEXT ---------- -------------------------------------------------------------------------------- 12 PL/SQL: SQL Statement ignored 14 PL/SQL: ORA-00942: table or view does not exist Elapsed: 00:00:00.17 – captjackvane Dec 12 '14 at 16:27
0

Looks like Tom Kyte has the answer: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:22541885338185

Gandolf, you were technically correct according to what Tom responds with, but for whatever reason (bug? user error?) I couldn't get that to work.

So I tried the workaround:

15:09:47 system@DEV> drop procedure system.kill_dev_session;

Procedure dropped.

Elapsed: 00:00:02.76
15:12:37 system@DEV> exit;
captjackvane@TomServo:~$ sqlplus sys@dev as sysdba

15:12:50 sys@DEV> create or replace procedure kill_dev_session (v_osname in varchar2)
is
 lv_env     VARCHAR2(30 char);
begin
  select replace(global_name, '.LEANLOGISTICS.COM', '') 
    into lv_env
    from global_name;

  FOR rec IN (select inst_id, sid, serial#
                from sys.gv$session
               where upper(osuser) = upper(v_osname)
              group by inst_id, sid, serial#)
  LOOP

  dbms_output.put_line('ALTER SYSTEM KILL SESSION '''||rec.sid||','||rec.serial#||','||rec.inst_id||'''');

  EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '''||rec.sid||','||rec.serial#||','||rec.inst_id||'''';
  END LOOP;

  EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''127.0.0.1''';

  UTL_MAIL.send(sender => lv_env||'@silver01.leanlogistics.com',
                recipients => 'dba@leanlogistics.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;
/

    Procedure created.

    Elapsed: 00:00:01.63
    15:12:52 sys@DEV> create public synonym kill_dev_session for sys.kill_dev_session;

    Synonym created.

    Elapsed: 00:00:00.80

    15:12:52 sys@DEV> grant execute on kill_dev_session to system;

    Grant succeeded.

    Elapsed: 00:00:00.80

This is working now. Of course, as you can see, I also had to change the inner sql to be a loop, because this wouldn't work if a user had logged in with 2+ session.

Additionally, it still isn't working because the very act of querying is doing a parallel query, which means that there are a whole bunch of "ghost" session ids that only exist at the time of the query, resulting in this procedure returning:

15:47:43 system@DEV> exec sys.kill_dev_session('captjackvane');
BEGIN sys.kill_dev_session('captjackvane'); END;
*
ERROR at line 1:
ORA-00026: missing or invalid session ID
ORA-06512: at "SYS.KILL_DEV_SESSION", line 17
ORA-06512: at line 1

But hey, what is life without a challenge? I'll work on that, then perhaps post another question to the forum if I can't resolve the issue myself. Thanks for the help.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.