The values of initialization parameters in another session can be changed  using procedures SET_BOOL_PARAM_IN_SESSION and SET_INT_PARAM_IN_SESSION provided in DBMS_SYSTEM package.

Let’s demonstrate:

SQL>conn / as sysdba

SYS> grant dba to hr;

– Currently parameter HASH_AREA_SIZE is set to 131073 in HR session

HR>> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131073

– Find out SID, SERIAL# for HR session

SYS> select sid, serial#, username from v$session where username=’HR';

SID    SERIAL# USERNAME
———- ———- ——————————
54        313 HR

– Set value of parameter HASH_AREA_SIZE to 131072 in HR session

SYS> exec dbms_system.SET_INT_PARAM_IN_SESSION(54, 313, ‘HASH_AREA_SIZE’,131072);

– Verify that the value of parameter HASH_AREA_SIZE has been changed in HR session

HR> sho parameter hash_area_size

NAME                                 TYPE        VALUE
———————————— ———– ——————————
hash_area_size                       integer     131072

Similary Boolean initialization parameters can be modified using  dbms_system.SET_BOOL_PARAM_IN_SESSION.

– Let’s find out the value of parameter SKIP_UNUSABLE_INDEXES in HR session

HR> sho parameter skip_unusable indexes

NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean TRUE

– Modify the  value of parameter SKIP_UNUSABLE_INDEXES to FALSE in HR session

SYS> exec dbms_system.SET_BOOL_PARAM_IN_SESSION(54, 313, ‘skip_unusable_indexes’,FALSE);

– Verify that the value of parameter SKIP_UNUSABLE_INDEXES  has been changed to FALSE in HR session

HR> sho parameter skip_unusable indexes

NAME TYPE VALUE
———————————— ———– ——————————
skip_unusable_indexes boolean FALSE

References:

http://dbaspot.com/oracle-server/143210-how-query-sessions-active-initialization-parameters-other-session.html

————————————————————————————————–

Related links:

Home

Database Index 

Find Values Of Another Session’s Parameters

—————————-