How to check Total, Active and Inactive sessions in Oracle database ?

To check the total, active, and inactive sessions in an Oracle database, you can query the V$SESSION view.

Checking Total Sessions

To get the total number of sessions:

SELECT COUNT(*) AS total_sessions
FROM v$session;

Checking Active Sessions

Active sessions are those that are currently executing SQL commands. To find the number of active sessions:

SELECT COUNT(*) AS active_sessions
FROM v$session
WHERE status = 'ACTIVE';

Checking Inactive Sessions

Inactive sessions are those that are connected to the database but not currently executing any SQL commands. To find the number of inactive sessions:

SELECT COUNT(*) AS inactive_sessions
FROM v$session
WHERE status = 'INACTIVE';

Combined Query

You can combine these queries into a single query to get the total, active, and inactive sessions in one result set:

SELECT 
    (SELECT COUNT(*) FROM v$session) AS total_sessions,
    (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') AS active_sessions,
    (SELECT COUNT(*) FROM v$session WHERE status = 'INACTIVE') AS inactive_sessions
FROM dual;

Detailed Steps

1. Open SQL*Plus or any SQL interface:

   sqlplus / as sysdba

2. Execute the Combined Query:

   SELECT 
       (SELECT COUNT(*) FROM v$session) AS total_sessions,
       (SELECT COUNT(*) FROM v$session WHERE status = 'ACTIVE') AS active_sessions,
       (SELECT COUNT(*) FROM v$session WHERE status = 'INACTIVE') AS inactive_sessions
   FROM dual;

Example Output

The output of the combined query might look like this:

• TOTAL_SESSIONS: Total number of sessions currently connected to the database.
• ACTIVE_SESSIONS: Number of sessions currently executing SQL commands.
• INACTIVE_SESSIONS: Number of sessions that are connected but not currently executing SQL commands.

Additional Information

If you need more details about the sessions, such as the user, program, or machine associated with each session, you can query the V$SESSION view directly:

SELECT sid, serial#, username, status, program, machine
FROM v$session
ORDER BY status;

This query will give you a detailed list of all sessions, their statuses, and additional information that might be useful for monitoring and troubleshooting.

Using these queries, you can effectively monitor the session activity in your Oracle database and gather insights into the current usage and performance.