Um pouco do pacote DBMS_APPLICATION_INFO ...

Olá,

Acredito que este artigo esteja mais direcionado para desenvolvedores de aplicações que fazem uso do banco de dados Oracle.

Problema:

Disponibilizar informações no Oracle, em tempo real, do que está sendo realizado por cada usuário de um sistema. Se um usuário acessar o banco de dado através de uma aplicação, deverá estar disponível para visualização, alguma informação que identifique o quê cada sessão está executando dentro da aplicação.

Solução:

Sabemos que a view dinâmica de desempenho V$SESSION nos oferece algumas informações das sessões que estão atualmente conectadas no banco de dados Oracle. Portanto, para resolver o problema acima, poderemos fazer o uso da view V$SESSION de forma a popular algumas colunas como MODULE, ACTION e CLIENT_INFO como demonstrarei mais abaixo.

Irei utilizar o pacote chamado DBMS_APPLICATION_INFO diponível no Oracle, na qual farei uso das procedures SET_MODULE e SET_CLIENT_INFO como demonstrado abaixo:

SQL> desc DBMS_APPLICATION_INFO;

PROCEDURE READ_CLIENT_INFO
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                OUT
PROCEDURE READ_MODULE
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                OUT
 ACTION_NAME                    VARCHAR2                OUT
PROCEDURE SET_ACTION
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_CLIENT_INFO
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 CLIENT_INFO                    VARCHAR2                IN
PROCEDURE SET_MODULE
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 MODULE_NAME                    VARCHAR2                IN
 ACTION_NAME                    VARCHAR2                IN
PROCEDURE SET_SESSION_LONGOPS
 Nome do Argumento              Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 RINDEX                         BINARY_INTEGER          IN/OUT
 SLNO                           BINARY_INTEGER          IN/OUT
 OP_NAME                        VARCHAR2                IN     DEFAULT
 TARGET                         BINARY_INTEGER          IN     DEFAULT
 CONTEXT                        BINARY_INTEGER          IN     DEFAULT
 SOFAR                          NUMBER                  IN     DEFAULT
 TOTALWORK                      NUMBER                  IN     DEFAULT
 TARGET_DESC                    VARCHAR2                IN     DEFAULT
 UNITS                          VARCHAR2                IN     DEFAULT

Imagine que estamos desenvolvendo um sistema de Controle de Estoque. Agora imagine um relatório fornecido pelo sistema que consolida as entradas e saídas de todos os materiais de um almoxarifado e que esse relatório possa ou não levar um tempo considerável para ser executado. Se quisermos visualizar no banco de dados através da view V$SESSION as sessões que estão executando este relatório, precisaríamos então incluir no fonte do sistema (antes do usuário submeter a execução do relatório), o comando abaixo:

Irei realizar uma simulação como se o sistema em uso fosse o próprio SQL*PLUS, utilizando o usuário SCOTT para realizar a conexão com o banco de dados:

SQL> exec dbms_application_info.set_module(
          module_name => 'Controle de Estoque',
          action_name => 'Executando Relatório de Materiais');

Procedimento PL/SQL concluído com sucesso.

Ao executar o procedimento acima, poderemos então obter tais informações selecionando as mesmas da view dinâmica de desempenho V$SESSION:

SQL> select osuser,machine,program,module,action from 
  2  v$session where username='SCOTT';

OSUSER     MACHINE     PROGRAM      MODULE              ACTION
---------- ----------- ------------ ------------------- ---------------------------------
legatti    MICRO_001   sqlplus.exe  Controle de Estoque Executando Relatório de Materiais

Como demonstrado acima, podemos perceber que as colunas MODULE e ACTION foram populadas. Em resumo, poderemos então utilizar o pacote DBMS_APPLICATION_INFO em nossas aplicações de forma a disponibilizar ao administrador do sistema ou até mesmo ao DBA, uma opção para visualizar em tempo real "quem" está fazendo "o quê" dentro do sistema em questão.

Caso seja necessário disponibilizar mais informações, poderemos também fazer uso da coluna CLIENT_INFO como demonstrado abaixo:

SQL> exec dbms_application_info.set_client_info('data, hora, meu IP, etc...');

Procedimento PL/SQL concluído com sucesso.

SQL> select client_info from v$session where sid=(select sid from v$mystat where rownum=1);

CLIENT_INFO
--------------------------------
data, hora, meu IP, etc...

Como eu citei acima a informação de endereço IP, poderemos também fazer uso da função SYS_CONTEXT de forma a acessar esta e outras informações da sessão em questão:

SQL> select SYS_CONTEXT('USERENV', 'TERMINAL') micro,
  2             SYS_CONTEXT('USERENV', 'IP_ADDRESS') ip,
  3             SYS_CONTEXT('USERENV', 'OS_USER') usuario_rede from dual;

MICRO          IP             USUARIO_REDE
-------------- -------------- --------------
MICRO_001      192.168.1.10   legatti

No mais, restaria ao desenvolvedor encontrar a forma mais apropriada de implementar tal solução na linguagem de programação ou framework em uso (Java, PHP, Delphi, etc...).