Re: Applications tags and identifiers on V$SESSION

Hi Luis,
The CLIENT ID is the best way to identify the end-user. It is even propagated through database links. And can be set on modern JDBC with Connection.setClientInfo(OCSID.CLIENTID) to avoid additional calls. Regards,
Franck.

On Wed, Apr 10, 2019 at 1:48 PM Luis Santos <lsantos_at_pobox.com> wrote:

> I always recommend to our developers to make extensive use of
> DBMS_APPLICATION_INFO. As far I know there are three main columns on
> V$SESSION that are updated when using DBMS_APPLICATION_INFO:
>
> SQL> exec dbms_application_info.set_client_info (CLIENT_INFO => 'MY
>> PROCESS');
>> SQL> exec dbms_application_info.set_module (MODULE_NAME => 'MY
>> MODULE', ACTION_NAME => 'ACTION_01');
>> SQL>
>> SQL> SELECT CLIENT_INFO, MODULE, ACTION FROM v$session WHERE audsid =
>> USERENV('SESSIONID');
>> CLIENT_INFO MODULE ACTION
>> -------------------- -------------------- --------------------
>> MY PROCESS MY MODULE ACTION_01
>> SQL>
>> SQL> exec dbms_application_info.set_action (ACTION_NAME => 'ACTION
>> 02');
>> SQL>
>> SQL> SELECT CLIENT_INFO, MODULE, ACTION FROM v$session WHERE audsid =
>> USERENV('SESSIONID');
>> CLIENT_INFO MODULE ACTION
>> -------------------- -------------------- --------------------
>> MY PROCESS MY MODULE ACTION 02
>
>
> But today a developer told me that he is using
> *dbms_session.set_identifier.*
>
> I have to confess I never heard about this option before.
>
> This set another column on V$SESSION, called CLIENT_IDENTIFIER.
>
> SQL> exec dbms_session.set_identifier (CLIENT_ID => 'MY PROCESS AGAIN');
>>
>> SQL> SELECT CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, ACTION FROM v$session
>> WHERE audsid = USERENV('SESSIONID');
>> CLIENT_INFO CLIENT_IDENTIFIER MODULE ACTION
>> -------------------- -------------------- --------------------
>> --------------------
>> MY PROCESS MY PROCESS AGAIN MY MODULE ACTION 02
>
>
> And I discover, to my surprise, that CLIENT_IDENTIFIER /
> DBMS_SESSION.SET_IDENTIFIER seems a too much better option. Because
> CLIENT_INFO is not stored on v$active_session_history, but
> CLIENT_IDENITIFER is (although shortened to "CLIENT_ID").
>
> SQL> SELECT CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, ACTION FROM
>> v$active_session_history where rownum <=1;
>> SELECT CLIENT_INFO, CLIENT_IDENTIFIER, MODULE, ACTION FROM
>> v$active_session_history where rownum <=1
>> *
>> ERRO na linha 1:
>> ORA-00904: "CLIENT_IDENTIFIER": identificador inválido
>>
>> SQL> SELECT CLIENT_INFO, CLIENT_ID , MODULE, ACTION FROM
>> v$active_session_history where rownum <=1;
>> SELECT CLIENT_INFO, CLIENT_ID , MODULE, ACTION FROM
>> v$active_session_history where rownum <=1
>> *
>> ERRO na linha 1:
>> ORA-00904: "CLIENT_INFO": identificador inválido
>>
>> SQL> SELECT CLIENT_ID , MODULE, ACTION FROM v$active_session_history
>> where rownum <=1;
>> CLIENT_ID MODULE
>> ACTION
>> ----------------------------------------------------------------
>> -------------------- --------------------
>> 93316716
>> REGISTROSOLIC_TAP SOLICITACAO_TAP
>
>
>
> *--*
> *Att*
>
>
> *Luis Santos*
>
>

--
http://www.freelists.org/webpage/oracle-l