Reason of too many inactive sessions and clean them in Oracle

How to Clean Up Inactive Sessions in Oracle Database

If you are an Oracle database administrator, you may have encountered the problem of too many inactive sessions in your database. Inactive sessions are those that are not executing any SQL statements or performing any other activity, but still occupy memory and other resources. These sessions can affect the performance and availability of your database, especially if they reach the maximum limit of concurrent sessions.

In this blog post, I will show you how to identify and clean up inactive sessions in Oracle database using some simple SQL commands and configuration settings. By following these steps, you can improve the efficiency and security of your database.

Step 1: Check the Number and Details of Inactive Sessions

The first step is to find out how many inactive sessions are present in your database and what are their characteristics. You can use the following SQL queries to get this information from the v$session and v$process views:

-- Check the number of inactive and active sessions

select status, count(1) from v$session group by status;

-- Check the username and program name of inactive sessions

select username, program, count(1) from v$session where status='INACTIVE' group by username, program;

-- Find more details of inactive sessions, such as OS username, terminal, process ID, etc.

select p.username "OSUSERNAME", p.terminal,p.program,
s.username "DBUSERNAME",s.command,s.status,
s.server,s.process,s.machine,s.port,s.terminal,s.program,
s.sid,s.serial#,p.spid FROM v$session s,v$process p
WHERE p.addr=s.paddr and s.status='INACTIVE'
order by 1,4;

These queries will help you understand the source and nature of the inactive sessions and decide whether they need to be terminated or not.

Step 2: Identify the Cause of Inactive Sessions

The next step is to determine why the sessions are inactive and how to prevent them from accumulating. There are two main causes of inactive sessions: dead connections and idle connections.

Dead Connections

Dead connections are those that have been terminated abruptly by the client or the network, but the server is not aware of it. This can happen due to various reasons, such as power outage, network failure, application crash, etc. The server still maintains the session information and resources until it detects the disconnection.

To avoid dead connections, you can configure the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file on the client side. This parameter specifies the interval in minutes after which the client sends a probe packet to the server to check the connection status. If the server does not respond, the client closes the session and frees the resources.

To set the SQLNET.EXPIRE_TIME parameter, open the sqlnet.ora file on the client machine and add the following line:

SQLNET.EXPIRE_TIME=60

This will set the probe interval to 60 minutes. You can adjust the value according to your needs, but keep in mind that a lower value may increase the network traffic and a higher value may delay the detection of dead connections.

Idle Connections

Idle connections are those that are still alive but not performing any activity for a long time. This can happen due to various reasons, such as user inactivity, application design, batch processing, etc. The server does not terminate these sessions unless they exceed the maximum limit of concurrent sessions or the idle time limit.

To avoid idle connections, you can create user profiles with the IDLE_TIME limit and assign them to the users who are likely to create idle sessions. The IDLE_TIME limit specifies the maximum time in minutes that a session can remain idle before it is automatically terminated by the server.

To create a user profile with the IDLE_TIME limit, you need to enable the resource limit feature on the server by executing the following command:

alter system set resource_limit=true scope=both;

Then, you can create a user profile with the desired IDLE_TIME limit by executing the following command:

alter profile customers_profiles limit idle_time 30;

This will create a user profile named customers_profiles with the IDLE_TIME limit of 30 minutes. You can adjust the value according to your needs, but keep in mind that a lower value may cause unwanted session termination and a higher value may not be effective in reducing idle sessions.

To assign the user profile to the users, you can execute the following command:

alter user customer1 profile customers_profiles;

This will assign the user profile customers_profiles to the user customer1. You can repeat this command for other users who need the same profile.

Note: The IDLE_TIME limit only applies to sessions that are inactive, not to sessions that are executing long-running queries or transactions. If you want to limit the execution time of queries or transactions, you can use the CPU_PER_SESSION or CPU_PER_CALL limits instead.

Conclusion

In this blog post, I have shown you how to clean up inactive sessions in Oracle database using some simple SQL commands and configuration settings. By following these steps, you can improve the performance and availability of your database and avoid potential issues such as resource exhaustion, session limit violation, security breach, etc.

I hope you found this post useful and informative. If you have any questions or feedback, please feel free to leave a comment below. Thank you for reading!

---