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,

Alex Zaballa.