oracle连接数详解

oracle连接数默认是150,当同一时间连接数大于这个数的时候就会报错,现在的解决办法为增大oracle的连接数,从150改为1500,操作如下

1.修改前问题解决

如果已经报超过连接数错误后的话,是登录不上oracle的,所以需要用命令 lsof -i:1521 查看,所有占用这个端口的进程,然后用 kill -9 PID 杀死这些进程,有可能会很多,不想一个一个杀死可以百度谷歌方法

2.登录oracle

1
2
3
#切换到oracle,再登录
su - oracle
sqlplus / as sysdba

3.查询连接数,并修改

3.1使用命令 show parameter processes;

1
2
3
4
5
6
7
8
9
10
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 150

最后一行为连接数150

3.2修改前需先创建 spfile 并重启 oracle 服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> create spfile from pfile;
File created.

#关闭 oracle 服务
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

#启动oracle服务
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.

3.3修改连接数为1500

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SQL> alter system set processes=1500 scope = spfile;
System altered.

#oracle操作要养成提交的习惯
SQL> commit;
Commit complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 541068368 bytes
Database Buffers 289406976 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.

3.4最后验证连接数是否修改成功

1
2
3
4
5
6
7
8
9
10
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 1000
log_archive_max_processes integer 4
processes integer 1500

已改为1500,说明修改成功,现在可以多人痛快的连接登录oracle了

说在最后,如果有什么问题,请留言,我会尽量在第一时间给您答复