--View session session: select * from v$session where username is not null select username,count(username) from v$session where username is not null group by username --View current connections select count(*) from v$process; --View maximum connections select value from v$parameter where name ='processes'; --View the number of concurrent connections Select count(*) from v$session where status='ACTIVE'; --Query: client device ID, client program oracle User name, number of connections consumed select b.MACHINE machine name, b.PROGRAM Program name, b.USERNAME User name, count(*) Connection number from v$process a, v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE, b.PROGRAM, b.USERNAME order by count(*) desc; --Kill the specified process alter system kill session 'sid,serial#' immediate;
1. Release resources
stay sqlnet.ora Set in file expire_time Parameters. //You can use the exit? Time parameter to intermittently check for abnormal session s and release the process. //Official note: sqlnet.express? Time Purpose Use parameter SQLNET.EXPIRE_TIME to specify a the time interval, in minutes, to send a probe to verify that client/server connections are active. Setting a value greater than 0 ensures that connections are not left open indefinitely, due to an abnormal client termination. If the probe finds a terminated connection, or a connection that is no longer in use, it returns an error, causing the server process to exit. This parameter is primarily intended for the database server, which typically handles multiple connections at any one time. Limitations on using this terminated connection detection feature are: It is not allowed on bequeathed connections. Though very small, a probe packet generates additional traffic that may downgrade network performance. Depending on which operating system is in use, the server may need to perform additional processing to distinguish the connection probing event from other events that occur. This can also result in degraded network performance. Recommended Value 10 Example SQLNET.EXPIRE_TIME=10 //The setting here is 10 minutes. Every 10 minutes, Oracle will confirm whether all session client connections are normal. For abnormal sessions, Oracle will clean up the process.
2. Expand the number of connections, and the Session will automatically expand
alter system set processes = 500 scope = spfile;
3. After the above optimization operation is set, restart the database, and close all connections when shutting down (use immediate shutdown)
SQL>conn /as sysdba SQL>shutdown immediate SQL>startup
If there are many connections or sessions, they may be closed slowly. If they cannot be closed, please refer to: https://www.cnblogs.com/kerrycode/p/3435581.html
Several connections, processes and sessions are mentioned above. Here is a brief supplement to the relationship between the two:
Each sql login is called a connection, and each connection can generate one or more sessions. If the database is running in a dedicated server mode, A session corresponds to a server process. If the database runs in shared server mode, a server process can serve multiple sessions. Connection is not built directly between the user process and the database instance. It's between the user process and the Server Process, Therefore, if there is a Connection, there must be a user process and a server process, but there may not be a Session. For example, if you need to transport things from A to B, Connection can be regarded as A "bridge", and the truck will transport things from A to B and return to A, which is Session. Therefore, as long as you keep the connection open, you can create a session on the connection at any time.