Como alterar o valor de um parâmetro de outra sessão no Oracle

Como alterar o valor de um parâmetro de outra sessão

Imaginem um cenário onde é necessário alterar o valor de um parâmetro de uma sessão que já está aberta no banco de dados e sem uma interface SQL para realizar um ALTER SESSION.

Para resolver este problema, podemos utilizar as procedures SET_INT_PARAM_IN_SESSION e SET_BOOL_PARAM_IN_SESSION da package DBMS_SYSTEM.

dbms_system.set_bool_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
bval IN BOOLEAN);

dbms_system.set_int_param_in_session(
sid IN NUMBER,
serial# IN NUMBER,
parnam IN VARCHAR2,
intval IN BINARY_INTEGER);

Neste exemplo, vamos alterar o parâmetro sort_area_size da sessão abaixo:

[oracle@oracle01 ~]$ sqlplus scott/tiger

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 20 11:08:29 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Thu Nov 20 2014 11:07:59 +01:00
Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> show parameter sort_area

NAME           TYPE VALUE
------------------ --------------
sort_area_size integer 65536

Buscando as informações e alterando o valor do parâmetro SORT_AREA_SIZE da sessão do usuário SCOTT

[oracle@oracle01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 20 11:13:08 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select sid,serial# from v$session where username='SCOTT';

 SID  SERIAL#
---------- ----------
 237        49369

SQL> exec dbms_system.SET_INT_PARAM_IN_SESSION(237, 49369, 'SORT_AREA_SIZE',131072);

PL/SQL procedure successfully completed.

Verificando o novo valor do parâmetro SORT_AREA_SIZE na sessão do usuário SCOTT:

SQL> select name, value
 from V$SES_OPTIMIZER_ENV
 where sid=237
 and name='sort_area_size';

NAME           VALUE
-------------- -----------
sort_area_size 131072

Abraço