oracle-session-统计某时间段连接数据库的客户端信息-非精准

统计某时间段连接数据库的客户端信息-非精准

适用于：迁移数据库同时ip变更时，统计原数据库的连接信息和dblink连接信息，以方便迁移后，修改相关dblink及申请安全开通网络权限
DBA_HIST_ACTIVE_SESS_HISTORY 可换成 v$active_session_history

查看统计的时间范围

set line 300
SELECT MIN (sample_time), MAX (sample_time) FROM dba_hist_active_sess_history;

MIN(SAMPLE_TIME)                                   MAX(SAMPLE_TIME)                                  
-------------------------------------------------- --------------------------------------------------
2022/2/23 1:59:58.022                              2022/3/4 15:59:54.789                             
已选择 1 行。

统计客户端连接的用户名，机器名，采样的次数(可指定区间，是否排除dblink)

--查看job连接

select * from DBA_HIST_ACTIVE_SESS_HISTORY where user_id=0 and session_type='FOREGROUND'  and program like 'oracle@%J%'   and machine  IN (SELECT host_name FROM v$instance) ;

--查看dblink连接

select  *   FROM DBA_HIST_ACTIVE_SESS_HISTORY where  program like 'oracle@%'   and  machine not IN (SELECT host_name FROM v$instance);   --dblink

1. 只统计非dblink,非job,非后台进程的连接

set line 300 pagesize 4000
  SELECT u.username,
         d.user_id,
         d.machine,
         COUNT (*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY d, dba_users u
   WHERE     d.user_id = u.user_id
         AND u.username NOT IN ('VIVO_DB_MONITOR')
         and d.session_type='FOREGROUND'
         AND d.user_id > 0   
         and  machine NOT IN (SELECT host_name FROM v$instance)  
         and  d.program not like 'oracle@%J%' ----排除job连接
         AND  d.program  not LIKE 'oracle@%'   ----排除dblink连接
         --AND a.sample_time >TO_DATE ('2022/01/01 09:29:01', 'yyyy/mm/dd hh24:mi:ss') AND a.sample_time < TO_DATE ('2021/12/14 09:30:01', 'yyyy/mm/dd hh24:mi:ss') ----指定区间
GROUP BY u.username, d.user_id, d.machine
ORDER BY 4 DESC ;


USERNAME                          USER_ID MACHINE                                                            COUNT(*)
------------------------------ ---------- ---------------------------------------------------------------- ----------
abc                                    97 abc                                                                 21444

2. 只统计dblink连接

set line 300 pagesize 4000
  SELECT u.username,
         d.user_id,
         d.machine,
         COUNT (*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY d, dba_users u
   WHERE     d.user_id = u.user_id
         AND u.username NOT IN ('VIVO_DB_MONITOR')
         and d.session_type='FOREGROUND'
         AND d.user_id > 0   
         and  machine NOT IN (SELECT host_name FROM v$instance)  
         and  d.program not like 'oracle@%J%' ----排除job
         AND  d.program  LIKE 'oracle@%'   ----只统计dblink连接
         --AND a.sample_time >TO_DATE ('2022/01/01 09:29:01', 'yyyy/mm/dd hh24:mi:ss') AND a.sample_time < TO_DATE ('2021/12/14 09:30:01', 'yyyy/mm/dd hh24:mi:ss') ----指定区间
GROUP BY u.username, d.user_id, d.machine
ORDER BY 4 DESC ;



USERNAME                          USER_ID MACHINE                                                            COUNT(*)
------------------------------ ---------- ---------------------------------------------------------------- ----------
abc                                    97  abc                                                                 21444