Packages

PROCEDURE DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION

This procedure sets a Boolean parameter in another session

Note that this procedure does not work for the SQL_TRACE parameter.

For example to set the HASH_JOIN_ENABLED parameter to FALSE in a session with SID 9, serial# 29 use use

    EXECUTE dbms_system.set_bool_param_in_session (9, 29, 'hash_join_enabled', FALSE);

The SID and serial# for the session can be obtained from V$SESSION e.g.

    DECLARE
        l_sid NUMBER;
        l_serial NUMBER;
    BEGIN
        SELECT sid, serial#
        INTO l_sid, l_serial
        FROM v$session
        WHERE username = 'User1';

        dbms_system.set_bool_param_in_session 
        (l_sid, l_serial, 'hash_join_enabled', FALSE);
    END;
    /