Applications tags and identifiers on V$SESSION

From: Luis Santos <lsantos_at_pobox.com>
Date: Wed, 10 Apr 2019 09:47:13 -0300
Message-ID: <CAPWdmV8E2t65RUAbKfXr4HrfheCca5=ztsW2gepgbkYHzsoYNg_at_mail.gmail.com>



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
Received on Wed Apr 10 2019 - 14:47:13 CEST

Original text of this message