When managing an Oracle database, there are times when you need to issue a SHUTDOWN IMMEDIATE command. Before doing so, it’s essential to identify all active sessions to avoid disrupting critical operations. Here’s how you can list active and open connections in your Oracle database.

Query to List Active User Sessions

The following SQL query retrieves information about all active user sessions in the database:

SELECT 
       SUBSTR(a.spid, 1, 9) AS pid,
       SUBSTR(b.sid, 1, 5) AS sid,
       SUBSTR(b.serial#, 1, 5) AS ser#,
       SUBSTR(b.machine, 1, 6) AS box,
       SUBSTR(b.username, 1, 10) AS username,
       b.server,
       SUBSTR(b.osuser, 1, 8) AS os_user,
       SUBSTR(b.program, 1, 30) AS program
FROM   v$session b, v$process a
WHERE  b.paddr = a.addr
  AND  b.type = 'USER'
ORDER BY spid;

Explanation of the Query

1. Tables Used:
   • v$session: Contains session details for all current connections.
   • v$process: Links sessions to operating system processes.
2. Columns Selected:
   • spid: Operating system process ID of the session.
   • sid: Oracle session identifier.
   • serial#: Serial number to uniquely identify sessions.
   • machine: Host machine name.
   • username: Oracle username for the session.
   • server: Connection type (DEDICATED or SHARED).
   • osuser: Operating system user of the session.
   • program: Application program initiating the session.
3. Filters Applied:
   • b.type = 'USER': Filters to include only user sessions (ignores background processes).
4. Ordering:
   • Sorts results by spid for clarity.

Sample Output

Use Cases

1. Before shutting down the database, identify active users and notify them before issuing a shutdown.
2. Monitoring User Activity: Track which users or programs are connected to your database.
3. Performance Troubleshooting: Identify long-running or resource-heavy sessions.

Tips for Managing Sessions

1. Kill inactive or unnecessary sessions:
   If a session is idle but consuming resources, you can terminate it:

   ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
   

2. View System Sessions: To include all sessions (user and system processes), remove the b.type = 'USER' filter.
3. Monitor Continuously: Use tools like Oracle Enterprise Manager or custom scripts to automate session monitoring.

Conclusion

By running the above SQL query, you can effectively list all active and open connections in your Oracle database. This ensures you can monitor user activity and prevent disruptions during database maintenance.

Would you like additional tips on managing user sessions or monitoring database activity? 😊

Ahmad Gohar

With over 18 years of experience in software architecture, Java technologies, and leadership, I specialize in crafting scalable, future-proof solutions for global organizations. Whether it’s transforming legacy systems, building cutting-edge cloud-native applications, or mentoring teams to excel, I’m committed to delivering value-driven results.

See Full Bio