DBMS_APPLICATION_INFO.SET_CLIENT_INFO不起作用 [英] DBMS_APPLICATION_INFO.SET_CLIENT_INFO not working

问题描述

我需要设置client_info值以区分不同的客户端.

I need to set the client_info value to differentiate between different clients.

这些是我要测试的脚本.

These are the scripts that I am running to test.

第1步:SYS SCHEMA

Step 1:SYS SCHEMA

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

步骤2:HR SCHEMA

Step 2:HR SCHEMA

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(UTL_INADDR.GET_HOST_NAME );
EXEC DBMS_APPLICATION_INFO.SET_MODULE( 'CHECK','select' );

select                                      
    a.FIRST_NAME || ' ' || LAST_NAME AS FULL_NAME
from                                        
    HR.EMPLOYEES a                                      
where                                       
    a.DEPARTMENT_ID = '40'
;

第3步:SYS SCHEMA

Step 3:SYS SCHEMA

select
    *
from
    (
        select
           a.SQL_ID
          ,a.SQL_FULLTEXT
          ,to_char( a.LAST_ACTIVE_TIME,'DD-MON-YYYY HH24:MI:SS' )                                                                                as LAST_ACTIVE_TIME
          ,a.SERVICE
          ,b.SCHEMANAME
          ,b.CLIENT_INFO
          ,a.MODULE
          ,a.ACTION
        from
            GV$SQL a
        left outer join GV$SESSION b
        on
            (
                b.SQL_ID = a.SQL_ID
            )
        where
            a.EXECUTIONS != 0
    )
    c
where
    c.MODULE like '%CHECK%'
order by
    c.LAST_ACTIVE_TIME desc ;

"MODULE"和"ACTION"列正在获取值，但CLIENT_INFO没有显示任何内容.

The MODULE and ACTION columns are getting the values but the CLIENT_INFO is not showing anything.

有什么我想念的吗?

更新: 我也没有运气尝试过

Update: I have also tried with no luck

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO( SYS_CONTEXT('userenv','ip_address') );
EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO( '10.10.10.10' );

推荐答案

SET_CLIENT_INFO与会话相关，而不与单个SQL语句相关.

SET_CLIENT_INFO is related to a Session, not to a single SQL Statement.

执行此操作

第1步:SYS SCHEMA

Step 1:SYS SCHEMA

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;

步骤2:HR SCHEMA

Step 2:HR SCHEMA

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO(UTL_INADDR.GET_HOST_NAME );
EXEC DBMS_APPLICATION_INFO.SET_MODULE( 'CHECK','select' );

第3步:SYS SCHEMA

Step 3:SYS SCHEMA

SELECT
    a.SQL_ID
   ,a.CLIENT_INFO
   ,a.MODULE
   ,a.ACTION
   ,a.SCHEMANAME
   ,a.USERNAME
FROM
   V$SESSION a
WHERE
    a.MODULE = 'CHECK';

这篇关于DBMS_APPLICATION_INFO.SET_CLIENT_INFO不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！