Re: Applications tags and identifiers on V$SESSION

Hi Luis,

Yes, that is a well-known and recommended thing: https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvprxy.htm#i1010372 Moreover it also allows to gather and aggregate statistics by service/module/action,client_id using dbms_monitor and special views like v$serv_mod_act_stats and v$client_stats: https://docs.oracle.com/database/121/ARPLS/d_monitor.htm#ARPLS67154

You may noticed that even standard trace files do not store client_info:

*** 2019-03-21T01:49:01.531276+03:00
*** SESSION ID:(137.60357) 2019-03-21T01:49:01.531276+03:00
*** CLIENT ID:() 2019-03-21T01:49:01.531276+03:00
*** SERVICE NAME:() 2019-03-21T01:49:01.531276+03:00
*** MODULE NAME:(oradim.exe) 2019-03-21T01:49:01.531276+03:00
*** ACTION NAME:() 2019-03-21T01:49:01.531276+03:00
*** CLIENT DRIVER:() 2019-03-21T01:49:01.531276+03:00

 You can find column client_info just in a few views: v/gv$session, v/gv$sql_monitor, dba/all/user_sql_translations and sys_fba_context_aud.

On Wed, Apr 10, 2019 at 3: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