Script to Find Active Sessions in an Oracle Database

advertisements

_____________________________________________________________________________________________________________________

This script retrieves information about active sessions in the Oracle database. It selects session ID, session serial number, username, operating system username, machine name, program name, session status, session logon time, and the number of seconds since the last call.

By executing this script, you can gain insights into the active sessions in your Oracle database, including details such as which users are currently connected, the programs they are running, and the duration of their sessions. This information can be valuable for monitoring and managing database performance, identifying potential bottlenecks, and troubleshooting issues related to active sessions.

COLUMN session_id FORMAT 99999

COLUMN session_serial_number FORMAT 99999

advertisements

 

COLUMN username FORMAT A15

COLUMN os_username FORMAT A15

COLUMN machine FORMAT A20

COLUMN program FORMAT A30

COLUMN session_status FORMAT A10

COLUMN session_logon_time FORMAT A20

COLUMN seconds_since_last_call FORMAT 99999

SELECT

    s.sid AS session_id,

    s.serial# AS session_serial_number,

    s.username AS username,

    s.osuser AS os_username,

    s.machine AS machine,

    s.program AS program,

    s.status AS session_status,

    TO_CHAR(s.logon_time, 'DD-MON-YYYY HH24:MI:SS') AS session_logon_time,

    s.last_call_et AS seconds_since_last_call

FROM

    v$session s

WHERE

    s.status = 'ACTIVE';

SESSION_ID SESSION_SERIAL_NUMBER USERNAME       OS_USERNAME      MACHINE                PROGRAM                        SESSION_STATUS SESSION_LOGON_TIME    SECONDS_SINCE_LAST_CALL

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

       2345                12345 HR_USER        HRUSER01         HR-PC                  SQL Developer                  ACTIVE         26-JUN-2023 09:15:42                   120

       3456                54321 SYS            SYSUSER01        DB-SERVER              Oracle Enterprise Manager       ACTIVE         26-JUN-2023 10:45:18                    60

       4567                98765 APP_USER       APPUSER01        APP-SERVER             Custom Application             ACTIVE         26-JUN-2023 11:30:09                   180

_____________________________________________________________________________________________________________________

Website Stats