Oracle 11g Clientid_overwrite Event

A new Clientid_overwrite event has been added to Oracle 11g so that v$session can automatically react to setting the result after executing the set_client_info information for the Dbms_application_info package.

By default, after the Set_client_info procedure is set, the Dbms_session set_identifier procedure is also executed to enable v$session to reflect the result of the setting:

Sql> SELECT * from V$version;

BANNER

--------------------------------------------------------------------------------

Oracle database11genterprise Edition release11.2.0.1.0-64bit Production

Pl/sql Release 11.2.0.1.0-production

CORE 11.2.0.1.0 Production

TNS for Linux:version 11.2.0.1.0-production

Nlsrtl Version 11.2.0.1.0-production

Sql> SELECT SID, serial#, Client_identifier

2 from V$session

3 WHERE SID in (SELECT SID from V$mystat);

SID serial# Client_identifier

---------- ---------- ----------------------------------------------------------------

243 7966

Sql> EXEC Dbms_application_info. Set_client_info (' TEST ')

The PL/SQL process has completed successfully.

Sql> SELECT SID, serial#, Client_identifier

2 from V$session

3 WHERE SID in (SELECT SID from V$mystat);

SID serial# Client_identifier

---------- ---------- ----------------------------------------------------------------

243 7966

Sql> VAR STR VARCHAR2 (4000)

Sql> EXEC Dbms_application_info. Read_client_info (: STR)

The PL/SQL process has completed successfully.

Sql> PRINT STR

Str

This column more highlights: http://www.bianceng.cn/database/Oracle/

--------------------------------------------------------------------------------------

TEST

Sql> EXEC dbms_session. Set_identifier (' TEST ')

The PL/SQL process has completed successfully.

Sql> SELECT SID, serial#, Client_identifier

2 from V$session

3 WHERE SID in (SELECT SID from V$mystat);

SID serial# Client_identifier

---------- ---------- ----------------------------------------------------------------

243 7966 TEST

The client identity set using the Dbms_application_info package can only be obtained through the read_client_info of this package, and if the result you want to set is displayed directly in the V$session view, you may set events Clientid_ OVERWRITE:

Sql> ALTER session SET EVENTS ' Clientid_overwrite ';

The session has changed.

Sql> EXEC Dbms_application_info. Set_client_info (' test_new ')

The PL/SQL process has completed successfully.

Sql> SELECT SID, serial#, Client_identifier

2 from V$session

3 WHERE SID in (SELECT SID from V$mystat);

SID serial# Client_identifier

---------- ---------- ----------------------------------------------------------------

243 7966 Test_new

Cancel this event by using the following statement:

Sql> ALTER session SET the EVENTS ' clientid_overwrite off ';

The session has changed.