Sess user sessions

Fiche express
Type	Script SQL*Plus de diagnostic (Sessions)
Voir aussi	Sess users · Sess user stats

Sess user sessions donne une synthèse chiffrée des connexions à l'instance : nombre de sessions ouvertes rapporté au maximum autorisé (paramètre SESSIONS, avec le taux d'utilisation en pourcentage), puis par utilisateur le nombre de connexions actives et inactives.

Script

SET TERMOUT OFF
COLUMN current_instance NEW_VALUE current_instance NOPRINT
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON

PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : User Sessions Summary Report                                |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+

SET FEEDBACK OFF
SET LINESIZE 180
SET PAGESIZE 50000
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN max_sess_allowed FORMAT 9,999,999     JUSTIFY r HEADING 'Max sessions allowed'
COLUMN num_sessions     FORMAT 9,999,999,999 JUSTIFY r HEADING 'Number of sessions'
COLUMN pct_utl          FORMAT a19            JUSTIFY r HEADING 'Percent Utilization'

SELECT
    TO_NUMBER(a.value)                                  max_sess_allowed
  , TO_NUMBER(COUNT(*))                                 num_sessions
  , LPAD(ROUND((COUNT(*) / a.value) * 100, 0) || '%', 19) pct_utl
FROM
    v$session   b
  , v$parameter a
WHERE
    a.name = 'sessions'
GROUP BY
    a.value
/

COLUMN username      FORMAT a15           JUSTIFY r HEADING 'Oracle User'
COLUMN num_user_sess FORMAT 9,999,999     JUSTIFY r HEADING 'Number of Logins'
COLUMN count_a       FORMAT 9,999,999     JUSTIFY r HEADING 'Active Logins'
COLUMN count_i       FORMAT 9,999,999     JUSTIFY r HEADING 'Inactive Logins'

BREAK ON REPORT
COMPUTE SUM OF num_user_sess count_a count_i ON REPORT

SELECT
    LPAD(NVL(sess.username, '[B.G. Process]'), 15) username
  , COUNT(*)          num_user_sess
  , NVL(act.count, 0)   count_a
  , NVL(inact.count, 0) count_i
FROM
    v$session sess
  , (SELECT COUNT(*) count, NVL(username, '[B.G. Process]') username
     FROM v$session WHERE status = 'ACTIVE' GROUP BY username)   act
  , (SELECT COUNT(*) count, NVL(username, '[B.G. Process]') username
     FROM v$session WHERE status = 'INACTIVE' GROUP BY username) inact
WHERE
      NVL(sess.username, '[B.G. Process]') = act.username(+)
  AND NVL(sess.username, '[B.G. Process]') = inact.username(+)
GROUP BY
    sess.username, act.count, inact.count
/

SET FEEDBACK ON

[B.G. Process] regroupe les sessions sans username (processus d'arrière-plan de l'instance : PMON, SMON, LGWR...), qui comptent dans le total mais ne sont pas des connexions applicatives.

Voir aussi

• Sess users — détail nominatif de chaque session (au lieu de la synthèse)
• Sess user stats — statistiques de consommation par session (CPU, I/O, mémoire)