Alterando parâmetros de outra sessão

Muitas aplicações que conectam no banco de dados funcionam de formas diferentes, com regras diferentes e necessidades diferentes. Desta forma, varias vezes tornasse necessário ajustar parâmetros específicos (dinâmicos) para os usuários da aplicação que conectam no banco de dados, visando melhorar a performance, segurança e atender necessidades distintas.

Uma solução simples e que normalmente é adotada para estes cenários é criar triggers de logon, onde se o usuário for X o parâmetro da sessão é Y, caso contrario utiliza-se os padrões da instance.

Mas e quando precisamos alterar os parâmetros de um sessão (usuário) já aberta pela aplicação? Também é possível! Basta utilizarmos a package DBMS_SYSTEM.

A DBMS_SYSTEM é um pacote que permite coletar informações sobre os eventos da sessão atual e manipular as sessões dos demais usuários para definir eventos e alterar os valores de determinados parâmetros. Ela fornece algumas das capacidades da DBMS_SESSION mas com a capacidade de afetar qualquer sessão.

Vejamos um exemplo prático.

Primeiramente vou abrir uma sessão com meu usuário (ANDERSON). Supondo que ela foi aberta através de uma aplicação, digamos simplesmente que não consigo alterar os parâmetros.. “alter session set …” ou que ela esta em meio a varias operações e não pode ser finalizada sendo que o parâmetro X ou Y necessita ser alterado.

c0825:oracle:c0828sta> sqlplus anderson
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 8 14:25:07 2013
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Enter password:
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
 
SQL>

Agora abrindo uma sessão como SYSDBA, vamos ver se meu usuário possui algum parâmetro diferente dos parâmetros estabelecidos na instance. Eles poderiam ter sido alterados por uma trigger de logon, por exemplo.

c0825:oracle:c0828sta> sqlplus / as sysdba
 
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jul 8 14:29:36 2013
 
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
 
SQL> set lines 200
SQL> col name for a40
SQL> col username for a10
SQL> col value for a15
SQL> select a.sid, c.username, a.name, a.value
      from v$ses_optimizer_env a
      join v$sys_optimizer_env b on a.id = b.id
      join v$session c on a.sid = c.sid
     where a.value <> b.value
       and c.username is not null
          --and c.username not in ('SYS','SYSTEM','DBSNMP')
       and c.username = 'ANDERSON'
     order by a.sid, a.name;
 
no rows selected

Nenhum parâmetro diferente dos parâmetros do sistema! Agora através desta minha sessão (SYS) vou alterar um parâmetro da outra sessão (ANDERSON). Para fazer isto vou utilizar o pacote DBMS_SYSTEM, porém tenho 2 subprogramas(procedures): SET_INT_PARAM_IN_SESSION = Utilizado para alterar parâmetros com valores numéricos(inteiros) em outra sessão.

DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION (
   sid       IN  NUMBER,
   serial#   IN  NUMBER,
   parnam    IN  VARCHAR2,
   intval    IN  BINARY_INTEGER);

SET_BOOL_PARAM_IN_SESSION = Utilizado para alterar parâmetros boolean(TRUE,FALSE) em outra sessão.

DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION (
   sid       IN  NUMBER,
   serial#   IN  NUMBER,
   parnam    IN  VARCHAR2,
   bval      IN  BOOLEAN);

SQL> show user
USER is "SYS"
SQL> select sid, serial# from
  2  v$session
  3  where username='ANDERSON';
 
       SID    SERIAL#
---------- ----------
       293       8786
 
SQL> exec sys.dbms_system.set_int_param_in_session(293,8786,'optimizer_index_cost_adj',50);
 
PL/SQL procedure successfully completed.

Voltamos para a sessão “ANDERSON” e executamos um select from dual apenas para registrar a mudança do parâmetro.

SQL> show user
USER is "ANDERSON"
SQL> select 1 from dual;
 
         1
----------
         1

Vejamos agora se a sessão “ANDERSON” possui algum parâmetro diferente:

SQL> show user
USER is "SYS"
SQL> select a.sid, c.username, a.name, a.value
  2    from v$ses_optimizer_env a
  3    join v$sys_optimizer_env b on a.id = b.id
  4    join v$session c on a.sid = c.sid
  5   where a.value <> b.value
  6     and c.username is not null
  7        --and c.username not in ('SYS','SYSTEM','DBSNMP')
  8     and c.username = 'ANDERSON'
  9   order by a.sid, a.name;
 
       SID USERNAME   NAME                                     VALUE
---------- ---------- ---------------------------------------- ---------------
       293 ANDERSON   optimizer_index_cost_adj                 50

Vamos fazer outro teste:

-- Verificando usuário/criando uma tabela
SQL> show user
USER is "ANDERSON"
SQL> create table teste(
  2  cod number);
 
Table created.
 
-- Criando um indice sobre a tabela criada
SQL> create index teste_idx1 on teste(cod);
 
Index created.
 
-- Inserindo um registro
SQL> insert into teste values (1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
-- Invalidando o indice
SQL> alter index teste_idx1 unusable;
 
Index altered.
 
-- Verificando o status do indice
SQL> select index_name,
  2  status
  3  from user_indexes
  4  where index_name='TESTE_IDX1';
 
INDEX_NAME                     STATUS
------------------------------ --------
TESTE_IDX1                     UNUSABLE
 
-- Consulta
SQL> select * from teste where cod=1;
 
       COD
----------
         1

Porque a consulta não retornou erro se o índice esta inválido? Pelo fato do parâmetro SKIP_UNUSABLE_INDEXES estar setado para TRUE, desta forma os índices inválidos(UNUSABLE) são ignorados. Agora vamos alterar o parâmetro boolean SKIP_UNUSABLE_INDEXES na sessão “ANDERSON” para FALSE:

SQL> show user
USER is "SYS"
SQL> show parameter skip_unusable_indexes
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes                boolean     TRUE
 
SQL> exec sys.dbms_system.set_bool_param_in_session(293,8786,'skip_unusable_indexes',false);
 
PL/SQL procedure successfully completed.
 
SQL> select a.sid, c.username, a.name, a.value
      from v$ses_optimizer_env a
      join v$sys_optimizer_env b on a.id = b.id
      join v$session c on a.sid = c.sid
     where a.value <> b.value
       and c.username is not null
          --and c.username not in ('SYS','SYSTEM','DBSNMP')
       and c.username = 'ANDERSON'
     order by a.sid, a.name;
 
         SID USERNAME   NAME                                     VALUE
---------- ---------- ---------------------------------------- ---------------
       293 ANDERSON   optimizer_index_cost_adj                 50

Observe que o parâmetro SKIP_UNUSABLE_INDEXES = FALSE ainda não foi validado (ATIVADO) na sessão “ANDERSON”. Vamos agora executar a mesma consulta anterior:

SQL> show user
USER is "ANDERSON"
SQL> select * from teste where cod=1;
select * from teste where cod=1
*
ERROR at line 1:
ORA-01502: index 'ANDERSON.TESTE_IDX1' or partition of such index is in unusable state

Observe que agora tivemos erro na consulta apontando que o índice TESTE_IDX1 esta inválido. Vejamos os parâmetros alterados da sessão “ANDERSON”:

SQL> show user
USER is "SYS"
SQL> select a.sid, c.username, a.name, a.value
      from v$ses_optimizer_env a
      join v$sys_optimizer_env b on a.id = b.id
      join v$session c on a.sid = c.sid
     where a.value <> b.value
       and c.username is not null
          --and c.username not in ('SYS','SYSTEM','DBSNMP')
       and c.username = 'ANDERSON'
     order by a.sid, a.name;
 
         SID USERNAME   NAME                                     VALUE
---------- ---------- ---------------------------------------- ---------------
       293 ANDERSON   optimizer_index_cost_adj                 50
       293 ANDERSON   skip_unusable_indexes                    false

Fique atento!: Para esta procedure não funciona para o parâmetro SQL_TRACE!