DBMS_SESSION: Gerenciamento e informações de sessão no Oracle

O pacote DBMS_SESSION fornece procedures e functions para gerenciar e obter informações sobre as sessões do usuário no banco de dados Oracle. Uma sessão é uma conexão lógica entre um cliente e um servidor, que pode ser identificada por um número de sessão único. Cada sessão pode executar transações, consultas, stores procedures e outras operações no banco de dados.

Principais recursos do DBMS_SESSION

O pacote DBMS_SESSION oferece os seguintes recursos principais:

Alterar o estado da sessão atual, como definir ou limpar o contexto da aplicação, alterar o nível de isolamento da transação, alterar o modo de auditoria, alterar o esquema atual, alterar o formato de data ou hora, etc.
Obter informações sobre a sessão atual, como o número de sessão, o nome do usuário, o identificador do cliente, o identificador do módulo, o identificador da ação, o identificador do terminal, o endereço IP, o tempo de conexão, o tempo de inatividade, o status da transação, etc.
Obter informações sobre outras sessões no banco de dados, como o número de sessões ativas, inativas ou bloqueadas, o consumo de recursos, o tempo de espera, o tipo de evento de espera, etc.
Matar ou desconectar outras sessões no banco de dados, especificando o número de sessão ou o identificador do cliente.
Gerar um identificador global exclusivo (GUID) para a sessão atual ou para uma sessão específica.

Exemplos de uso do DBMS_SESSION

A seguir, apresento alguns exemplos de uso do pacote DBMS_SESSION para realizar diferentes tarefas relacionadas ao gerenciamento e informação de sessão.

Definir o contexto da aplicação

O contexto da aplicação é um conjunto de pares nome-valor que podem ser definidos para a sessão atual e usados para armazenar informações específicas da aplicação, como preferências do usuário, parâmetros de configuração, dados de autenticação, etc. O contexto da aplicação pode ser acessado por meio de funções SQL ou PL/SQL, como SYS_CONTEXT ou DBMS_SESSION.GET_CONTEXT.

Para definir o contexto da aplicação, podemos usar a procedure DBMS_SESSION.SET_CONTEXT, que recebe os seguintes parâmetros:

namespace: o nome do espaço de nomes do contexto, que deve ser um identificador válido do PL/SQL.
attribute: o nome do atributo do contexto, que deve ser um identificador válido do PL/SQL.
value: o valor do atributo do contexto, que pode ser uma expressão SQL ou PL/SQL de qualquer tipo de dados.
username: o nome do usuário para o qual o contexto é definido, que pode ser NULL para indicar o usuário atual.
client_id: o identificador do cliente para o qual o contexto é definido, que pode ser NULL para indicar o cliente atual.

Por exemplo, o seguinte código define o contexto da aplicação com o espaço de nomes ‘MY_APP’ e os atributos ‘LANGUAGE’ e ‘CURRENCY’ para a sessão atual:

BEGIN
  DBMS_SESSION.SET_CONTEXT('MY_APP', 'LANGUAGE', 'PT-BR');
  DBMS_SESSION.SET_CONTEXT('MY_APP', 'CURRENCY', 'BRL');
END;
/

Para limpar o contexto da aplicação, podemos usar a procedure DBMS_SESSION.CLEAR_CONTEXT, que recebe os seguintes parâmetros:

namespace: o nome do espaço de nomes do contexto, que deve ser um identificador válido do PL/SQL.
attribute: o nome do atributo do contexto, que pode ser NULL para indicar todos os atributos do espaço de nomes.
username: o nome do usuário para o qual o contexto é limpo, que pode ser NULL para indicar o usuário atual.
client_id: o identificador do cliente para o qual o contexto é limpo, que pode ser NULL para indicar o cliente atual.

Por exemplo, o seguinte código limpa o contexto da aplicação com o espaço de nomes ‘MY_APP’ para a sessão atual:

BEGIN
  DBMS_SESSION.CLEAR_CONTEXT('MY_APP', NULL);
END;
/

Obter o número de sessão

O número de sessão é um identificador numérico único que é atribuído a cada sessão quando ela é estabelecida no banco de dados. O número de sessão pode ser usado para obter ou alterar informações sobre a sessão, como o status, o consumo de recursos, o tempo de espera, etc.

Para obter o número de sessão da sessão atual, podemos usar a function DBMS_SESSION.UNIQUE_SESSION_ID, que retorna um valor numérico.

Por exemplo, o seguinte código obtém o número de sessão da sessão atual e o exibe na tela:

DECLARE
  v_session_id NUMBER;
BEGIN
  v_session_id := DBMS_SESSION.UNIQUE_SESSION_ID;
  DBMS_OUTPUT.PUT_LINE('O número da sessão atual é ' || v_session_id);
END;
/

Obter o nome do usuário

O nome do usuário é o identificador do usuário que está conectado ao banco de dados na sessão atual. O nome do usuário pode ser usado para verificar as permissões, os privilégios, os papéis, os esquemas, etc. do usuário.

Para obter o nome do usuário da sessão atual, podemos usar a function DBMS_SESSION.USERNAME, que retorna um valor VARCHAR2.

Por exemplo, o seguinte código obtém o nome do usuário da sessão atual e o exibe na tela:

DECLARE
  v_username VARCHAR2(30);
BEGIN
  v_username := DBMS_SESSION.USERNAME;
  DBMS_OUTPUT.PUT_LINE('O nome do usuário atual é ' || v_username);
END;
/

Obter o identificador do cliente

O identificador do cliente é um valor alfanumérico que pode ser definido para a sessão atual e usado para identificar o cliente que está usando a sessão. O identificador do cliente pode ser útil para rastrear ou auditar as atividades do cliente no banco de dados.

Para obter o identificador do cliente da sessão atual, podemos usar a function DBMS_SESSION.CLIENT_IDENTIFIER, que retorna um valor VARCHAR2.

Por exemplo, o seguinte código obtém o identificador do cliente da sessão atual e o exibe na tela:

DECLARE
  v_client_id VARCHAR2(64);
BEGIN
  v_client_id := DBMS_SESSION.CLIENT_IDENTIFIER;
  DBMS_OUTPUT.PUT_LINE('O identificador do cliente atual é ' || v_client_id);
END;
/

Para definir o identificador do cliente para a sessão atual, podemos usar a procedure DBMS_SESSION.SET_IDENTIFIER, que recebe o seguinte parâmetro:

client_id: o valor do identificador do cliente, que deve ser um valor VARCHAR2 de até 64 caracteres.

Por exemplo, o seguinte código define o identificador do cliente para a sessão atual como ‘JOAO’:

BEGIN
  DBMS_SESSION.SET_IDENTIFIER('JOAO');
END;
/

Para limpar o identificador do cliente da sessão atual, podemos usar a procedure DBMS_SESSION.CLEAR_IDENTIFIER, que não recebe nenhum parâmetro.

Por exemplo, o seguinte código limpa o identificador do cliente da sessão atual:

BEGIN
  DBMS_SESSION.CLEAR_IDENTIFIER;
END;
/

Obter o identificador do módulo

O identificador do módulo é um valor alfanumérico que pode ser definido para a sessão atual e usado para identificar o módulo ou a aplicação que está usando a sessão. O identificador do módulo pode ser útil para rastrear ou auditar as atividades do módulo no banco de dados.

Para obter o identificador do módulo da sessão atual, podemos usar a function DBMS_SESSION.MODULE, que retorna um valor VARCHAR2.

Por exemplo, o seguinte código obtém o identificador do módulo da sessão atual e o exibe na tela:

DECLARE
  v_module VARCHAR2(48);
BEGIN
  v_module := DBMS_SESSION.MODULE;
  DBMS_OUTPUT.PUT_LINE('O identificador do módulo atual é ' || v_module);
END;
/

Para definir o identificador do módulo para a sessão atual, podemos usar a procedure DBMS_SESSION.SET_MODULE, que recebe os seguintes parâmetros:

module_name: o nome do módulo, que deve ser um valor VARCHAR2 de até 48 caracteres.
action_name: o nome da ação que está sendo executada pelo módulo, que deve ser um valor VARCHAR2 de até 32 caracteres ou NULL para indicar nenhuma ação.

Por exemplo, o seguinte código atribui o identificador do módulo à sessão atual como ‘MY_APP’ e a ação como ‘INSERT’:

BEGIN
  DBMS_SESSION.SET_MODULE('MY_APP', 'INSERT');
END;
/

Definir o nível de isolamento da transação

O nível de isolamento da transação é uma propriedade que determina como os dados são acessados e modificados por uma transação em relação a outras transações concorrentes. O nível de isolamento da transação pode afetar o desempenho, a consistência e a integridade dos dados no banco de dados.

Para definir o nível de isolamento da transação para a sessão atual, podemos usar a procedure DBMS_SESSION.SET_ISOLATION_LEVEL, que recebe o seguinte parâmetro:

isolation_level: o valor do nível de isolamento da transação, que deve ser um dos seguintes:
- ‘SERIALIZABLE’: indica que a transação é serializável, ou seja, ela vê uma imagem estática dos dados e não é afetada por outras transações concorrentes. Esse é o nível mais restritivo, mas também o mais seguro, pois evita problemas como leituras sujas, leituras não repetíveis e leituras fantasmas.
- ‘READ COMMITTED’: indica que a transação é de leitura confirmada, ou seja, ela vê os dados que foram confirmados por outras transações no momento da leitura. Esse é o nível padrão, mas também o menos restritivo, pois permite problemas como leituras não repetíveis e leituras fantasmas.
- ‘READ ONLY’: indica que a transação é de leitura apenas, ou seja, ela vê uma imagem estática dos dados e não pode modificá-los. Esse nível é semelhante ao serializável, mas com a restrição de que a transação não pode fazer nenhuma alteração no banco de dados.

Por exemplo, o seguinte código define o nível de isolamento da transação para a sessão atual como serializável:

BEGIN
  DBMS_SESSION.SET_ISOLATION_LEVEL('SERIALIZABLE');
END;
/

Obter o status da transação

O status da transação é uma propriedade que indica se a transação está ativa, inativa, preparada ou confirmada. O status da transação pode ser usado para controlar o fluxo da transação, como fazer commit, rollback ou savepoint.

Para obter o status da transação da sessão atual, podemos usar a function DBMS_SESSION.TXN_STATE, que retorna um valor VARCHAR2.

Por exemplo, o seguinte código obtém o status da transação da sessão atual e o exibe na tela:

DECLARE
  v_txn_state VARCHAR2(10);
BEGIN
  v_txn_state := DBMS_SESSION.TXN_STATE;
  DBMS_OUTPUT.PUT_LINE('O status da transação atual é ' || v_txn_state);
END;
/

Matar ou desconectar outra sessão

Em alguns casos, pode ser necessário matar ou desconectar outra sessão no banco de dados, por exemplo, se a sessão estiver consumindo muitos recursos, bloqueando outros usuários, causando erros, etc. Para isso, podemos usar a procedure DBMS_SESSION.KILL_SESSION, que recebe os seguintes parâmetros:

session_id: o número da sessão a ser morta ou desconectada, que deve ser um valor numérico.
serial_num: o número de série da sessão a ser morta ou desconectada, que deve ser um valor numérico.
option: a opção de como matar ou desconectar a sessão, que deve ser um dos seguintes:
- ‘IMMEDIATE’: indica que a sessão deve ser morta imediatamente, sem esperar que ela termine suas operações pendentes. Essa opção pode causar perda de dados ou inconsistência no banco de dados.
- ‘POST_TRANSACTION’: indica que a sessão deve ser desconectada após terminar sua transação atual. Essa opção é mais segura, pois preserva a integridade dos dados no banco de dados.

Por exemplo, o seguinte código mata a sessão com o número 123 e o número de série 456 imediatamente:

BEGIN
  DBMS_SESSION.KILL_SESSION(123, 456, 'IMMEDIATE');
END;
/

Gerar um identificador global exclusivo

Um identificador global exclusivo (GUID) é um valor alfanumérico de 32 caracteres que pode ser usado para identificar de forma única um objeto, como uma tabela, uma coluna, uma restrição, uma sequência, etc. Um GUID pode ser útil para garantir a unicidade dos dados, evitar conflitos de nomes, facilitar a replicação, etc.

Para gerar um GUID para a sessão atual ou para uma sessão específica, podemos usar a function DBMS_SESSION.GENERATE_GUID, que recebe o seguinte parâmetro:

session_id: o número da sessão para a qual o GUID deve ser gerado, que deve ser um valor numérico ou NULL para indicar a sessão atual.

Por exemplo, o seguinte código gera um GUID para a sessão atual e o exibe na tela:

DECLARE
  v_guid VARCHAR2(32);
BEGIN
  v_guid := DBMS_SESSION.GENERATE_GUID(NULL);
  DBMS_OUTPUT.PUT_LINE('O GUID gerado para a sessão atual é ' || v_guid);
END;
/

Conclusão

O pacote DBMS_SESSION é uma ferramenta poderosa e flexível para gerenciar e obter informações sobre as sessões do usuário no banco de dados Oracle. Com ele, podemos alterar o estado da sessão, obter informações sobre a sessão, matar ou desconectar outras sessões, gerar identificadores globais exclusivos e muito mais. O pacote DBMS_SESSION pode nos ajudar a melhorar o desempenho, a segurança, a auditoria e a rastreabilidade das nossas aplicações que usam o banco de dados Oracle.

Abs

Referências

Oracle Database PL/SQL Packages and Types Reference 19c, DBMS_SESSION, disponível em: https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SESSION.html
Oracle Database Concepts 19c, Sessions, disponível em: https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/index.html#Oracle®-Database
Oracle Database Administrator’s Guide 19c, Managing Sessions, disponível em: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/#Oracle®-Database

DBMS_SESSION: Gerenciamento e informações de sessão no Oracle