Modify the number of oracle connections

Oracle database is an efficient and powerful relational database. Users connect to the database to perform various operations. But sometimes the number of user connections exceeds the default maximum number of connections, so you need to modify the number of Oracle connections. This article will introduce how to modify the number of Oracle connections.

1. Confirm the current number of connections to the database

Before you start modifying the number of Oracle connections, you first need to confirm the current number of connections to the database. You can use the following statement to query:

SELECT COUNT(*) FROM v$session;

This statement will return the number of sessions currently connected to the database. If this number exceeds the default maximum number of connections, it needs to be modified.

2. Modify the number of Oracle connections

By default, the maximum number of Oracle connections is 150. If you need to add more connections, you can use the following command to modify it:

ALTER SYSTEM SET processes=200 SCOPE=SPFILE;

The above command will increase the maximum number of connections to 200. For the modification to take effect, you need to restart the database so that SPFILE can be reloaded:

SHUTDOWN IMMEDIATE;
STARTUP;

3. Check whether the maximum number of connections has been modified

After executing the above command, you first need to check the alert.log of the database. Whether the file reports a successful modification, such as:

Database Characterset is AL32UTF8
Control Files: /opt/oracle/ora11g/dbs/control01.ctl, /opt/oracle/ora11g/dbs/control02.ctl, /opt/oracle/ora11g/dbs/control03.ctl
...
Database mounted.
Database opened.
ALTER DATABASE MOUNT
Sat Feb 15 18:22:20 2020
ALTER SYSTEM SET processes=200 SCOPE=SPFILE;
Completed: ALTER SYSTEM SET processes=200 SCOPE=SPFILE
Sat Feb 15 18:30:01 2020
Shutting down instance (immediate)
License high water mark = 13
Sat Feb 15 18:30:01 2020
Instance terminated by USER, pid = 12239

进程已经被终止了。

Instance terminated normally

If the report reports a successful modification, you can use the following command to verify whether the maximum number of connections has been modified:

SHOW PARAMETER processes;

In Oracle 9i and higher versions, it will be displayed The modified maximum number of connections. In Oracle 8i and earlier versions, you need to use the following statement to view:

SELECT name, display_value 
FROM v$parameter 
WHERE name ='processes';

4. Complete the modification of the number of connections

If the maximum number of connections is successfully modified to 200, then you can Reconnect to the database to test. The current number of connections can be viewed using the following statement:

SELECT COUNT(*) FROM v$session;

Summary:

The number of Oracle connections can be easily modified by following the above steps. At the same time, we should pay attention not to have too many connections after modification, otherwise it will cause the database to crash. Before upgrading the maximum number of connections, you should first evaluate the current number of servers and applications to determine the actual maximum number of connections, and strengthen connection pool management in actual use to ensure the normal and efficient operation of the Oracle database.

The above is the detailed content of Modify the number of oracle connections. For more information, please follow other related articles on the PHP Chinese website!