Início / dba / Perguntas / 86030
Accepted
captjackvane
Asked: 2014-12-13 07:35:12 +0800 CST

ORA-00942 ao tentar selecionar de gv$session dentro do procedimento

  • 772

Fui incumbido de permitir que os desenvolvedores eliminem suas próprias sessões no banco de dados de desenvolvimento. Escrevi um procedimento, mas por algum motivo estou recebendo PL/SQL: ORA-00942: table or view does not existao tentar chamar gv$session. Aqui está o proc, sendo compilado como SYSTEM. Executando em um banco de dados RAC 11.2.0.4.3, 2 nós:

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 => '[email protected]',
              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;
/

Então aqui está o problema: todo desenvolvedor faz login com o mesmo usuário, então não posso realmente usar username. Não quero abrir o cobertor alter systempara cada usuário. Isso permitiria que um gerente de desenvolvimento passasse um nome de usuário (ou seja, jsmith) e encerrasse essa sessão, e nos enviaria um e-mail aos DBAs informando que isso aconteceu, para que possamos ver se os desenvolvedores estão fazendo isso com frequência.

Alguma ideia de por que estou vendo isso: Erros para 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

Desde já, obrigado!

oracle plsql

2 respostas

  1. Best Answer

    Tente fazer isso:

    GRANT SELECT ON gv_$session TO system;

    Como você está acessando uma tabela sys por sistema em um procedimento armazenado, você precisa de uma concessão direta na tabela subjacente.

    • 2

  2. Parece que Tom Kyte tem a resposta: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:22541885338185

    Gandolf, você estava tecnicamente correto de acordo com o que Tom responde, mas por qualquer motivo (bug? Erro do usuário?) Não consegui fazer isso funcionar.

    Então eu tentei a solução alternativa:

    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 => '[email protected]',
                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

    Isso está funcionando agora. Claro, como você pode ver, eu também tive que mudar o sql interno para ser um loop, porque isso não funcionaria se um usuário tivesse logado com 2+ sessões.

    Além disso, ainda não está funcionando porque o próprio ato de consultar está fazendo uma consulta paralela, o que significa que há um monte de IDs de sessão "fantasmas" que existem apenas no momento da consulta, resultando no retorno deste procedimento:

    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

    Mas ei, o que é a vida sem um desafio? Vou trabalhar nisso e, em seguida, talvez postar outra pergunta no fórum, se não conseguir resolver o problema sozinho. Obrigado pela ajuda.

    • 0

relate perguntas