Resetando o SESSIONID do database - ORACLE 11.2.0.4

Recentemente em atendimento a um cliente, detectamos um problema no qual a aplicação estava tentando criar um índice com 31 caracteres, ocasionando o erro ORA-00972 (ORA-00972: identifier is too long).

Apos uma análise do processo que estava ocasionando o erro em conjunto com o cliente, foi possível identificar que a aplicação estava utilizando entre outros o parâmetros o SESSIONID que estava na casa dos 10 dígitos (1000363632) para composição do nome do índice que estava sendo criado.

Por se tratar de uma aplicação legado com mais de 5 anos de uso, sem suporte e responsável pela emissão de notas fiscais, a criticidade do problema era grande e não tínhamos suporte da aplicação para resolução do problema, precisaríamos resolver de qualquer forma para evitar prejuízos maiores.

A solução seria realizar um reset do SESSIONID, que era um dos parâmetros utilizados na definição do índice a ser criado.  Após uma pesquisa e alguns testes, foi possível identificar que o SESSIONID é composto pela sequence AUDSES$. Após validar que a mesma é ciclica, ou seja, apos atingir o max_value ela irá zerar novamente, desta forma não derivamos ter grandes problemas em "resetar" a mesma.

O primeiro passo diante de uma alteração deste tipo, validar em um ambiente de homologação e sempre garantir um backup antes de aplicar em produção.

Para efetuar o processo, precisamos parar o ambiente e subir em modo restrito, evitando assim que novas conexões sejam abertas.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.

Total System Global Area 1,4832E+10 bytes
Fixed Size                  2267624 bytes
Variable Size            1,4462E+10 bytes
Database Buffers          335544320 bytes
Redo Buffers               31875072 bytes
Database mounted.

Database opened. 

Após iniciar a base em modo restrito, vamos consultar os valores atuais da sequence.

SQL> SET LINES 210
SQL> set lines 80
SQL> select * from dba_sequences where sequence_name='AUDSES$';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            AUDSES$                                 1 2000000000            1 Y N      10000  1000363632

Agora é preciso determinar o valor que vamos utilizar para incrementar a sequence. O comando abaixo nos trará o valor correto a ser utilizado.

SQL> select (max_value - last_number - 5) from dba_sequences where sequence_name='AUDSES$';

(MAX_VALUE-LAST_NUMBER-5)
-------------------------
                999636363

Apos termos o valor que vamos utilizar para para incrementar a sequence, basta altera-la e depois executar a sequence para que ela assuma o novo valor.

SQL> alter sequence audses$ increment by 999636363 cache 2;

Sequence altered.

SQL> select audses$.nextval from dual;

   NEXTVAL
----------
1999990001

SQL> select audses$.nextval from dual;

   NEXTVAL
----------
         1

Zerada a sequence, vamos ajusta-la para que incremente o valor em 1 e utilize um cache de 20.

SQL> alter sequence audses$ increment by 1 cache 20;

Sequence altered.

SQL> select * from dba_sequences where sequence_name='AUDSES$';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            AUDSES$                                 1 2000000000            1 Y N         20           2

Feito isso, basta agora disponibilizar o ambiente novamente que o SESSIONID estará "resetado".

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 6,4137E+10 bytes
Fixed Size                  2269072 bytes
Variable Size            2,8991E+10 bytes
Database Buffers         3,5031E+10 bytes
Redo Buffers              112762880 bytes
Database mounted.
Database opened.
SQL>
SQL> conn lamim/lamim
Connected.
SQL> Select userenv('SESSIONID') From Dual;

USERENV('SESSIONID')
--------------------
                 178

É isso pessoal, espero que este artigo possa ajuda-los. Um abraço.
Fonte:How To Reset AUDSES$ Sequence (Doc ID 443618.1)