How to Check Active and Inactive Sessions in the Oracle Database?

Oracle database is a popular RDBMS (relational database management system) due to its scalability and vast features. Monitoring and controlling database sessions can be a critical task for a database administrator. For instance, it is very important to keep a track of active and inactive sessions for optimizing database performance and preventing security threats.

This guide will explain how to check active and inactive sessions:

Using v$session
Using gv$session
For a Specific User

How to Check Active and Inactive Sessions in the Oracle Database?

To check active and inactive sessions in the Oracle, log in to the database as an administrator. For this post, the SQL developer is used, so make the connection or log in accordingly.

How to Check Active and Inactive Sessions Using v$session?

The “v$session” provides the information about sessions for the current instance only. The “SELECT” statement with the “v$session” can be used to check active and inactive sessions.

Check Active Sessions Using v$session

The “WHERE” clause can be used to apply a filter to check the active sessions while using the v$session table. The query is given below:

SELECT * FROM v$session WHERE STATUS = 'ACTIVE';

The above query will only display the rows where the value in the “STATUS” column is “ACTIVE”.

Output

The output displayed the active sessions.

Check Inactive Sessions Using v$session

The “WHERE” clause can be used to apply a filter to check the inactive sessions using v$session. The query is given below:

SELECT * FROM v$session WHERE STATUS = 'INACTIVE';

The above query will only display only those sessions that are currently “INACTIVE”.

Output

The output showed that there is no inactive session.

How to Check Active and Inactive Sessions Using gv$session?

The “gv$session” provides information about sessions for all instances. The “SELECT” statement with the “gv$session” can be used to display the information about sessions. The “WHERE” clause will be utilized to filter the active and inactive sessions.

Check Active Sessions Using gv$session

To check the active session, filter the data of the gv$session table, by selecting only the rows where the value in the “STATUS” column is “ACTIVE”. The query is given below:

SELECT * FROM gv$session WHERE STATUS='ACTIVE';

Output

The output showed that the active sessions have been filtered out.

Check Inactive Sessions Using gv$session

The “WHERE” clause can be used to select only the rows where the value in the “STATUS” column is “INACTIVE” in the gv$session table. The query is given below:

SELECT * FROM gv$session WHERE STATUS='INACTIVE';

Output

The output showed that there is no inactive session.

How to Check Active and Inactive Sessions of a Specific User?

In the Oracle database, the active and inactive sessions of a specific user can be checked by specifying the user name in the WHERE clause.

Check Active Sessions of a Specific User

The query to check the active session of a specific user is given below:

SELECT * FROM v$session WHERE STATUS = 'ACTIVE' AND SCHEMANAME = 'SYS';

In the above query, the username (schema name) is “SYS”.

Output

The output depicts the active session for the “SYS” user.

Check Inactive Sessions of a Specific User

To check the inactive session of a specific user, type the following query:

SELECT * FROM v$session WHERE STATUS = 'INACTIVE' AND SCHEMANAME = 'SYS';

Output

The screenshot displayed that there is no inactive session of the “SYS” user.

Conclusion

The active and inactive sessions in Oracle can be checked by using the “v$session” or “gv$session” table with the “SELECT” statement. To filter out the active or inactive sessions, the “WHERE” clause can be utilized with the “STATUS” column. If you want to check for active or inactive sessions for a specific user, you can specify the user name in the WHERE clause. This write-up has demonstrated a practical guide on how to check active and inactive sessions in the Oracle database.

How to Check Active and Inactive Sessions in the Oracle Database?