Oracle Database 最大连接数
当前的数据库连接数
SQL> select count(*) from v$process;
COUNT(*)
----------
37
数据库允许的最大连接数
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 550
SQL> select value from v$parameter where name = 'processes';
VALUE
--------------------------------------------------------------------------------
550
修改最大连接数
SQL> alter system set processes = 100 scope = spfile;
System altered.
当前的session连接数
SQL> select count(*) from v$session;
COUNT(*)
----------
28
并发连接数
SQL> select count(*) from v$session where status = 'ACTIVE';
COUNT(*)
----------
23
测试连接数
#查看当前的连接数:
SQL> select count(*) from v$session;
COUNT(*)
----------
30
#查看最大连接数:
SQL> show parameter processes;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 100
#建立200个连接:
[root@hzvscmdb sql]# more check_connect_oracle.sh
#!/bin/bash
#functions: connect oracle server test
#author:Tony Wang
#modify_date: 2015-06-03.
mkdir -p /home/tony/shell/sql/log #create the directory of log
LOG_DIR=/home/tony/shell/sql/log #log path
rm -rf $LOG_DIR/* #delete the expire log
USERNAME=hr #user
PASSWORD=pass #password
SIDNAME=tony1521 #TNS
icount=0
while [ "$icount" -lt 20 ]
do
{
count=0
while [ "$count" -lt 10 ]
do
LOG_FILE=$LOG_DIR/check_oracle_$icount.$count.log
echo `sqlplus $USERNAME/$PASSWORD@$SIDNAME << EOF
set echo off
set feedback off
spool $LOG_FILE
alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
select sysdate from dual;
spool off
set echo on
set feedback on
exit
EOF` >> $LOG_FILE
let count=$count+1
done
} &
let icount=$icount+1
done
#连接正确
[root@hzvscmdb log]# more check_oracle_0.0.log
SQL> alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';
SQL> select sysdate from dual;
SYSDATE
-------------------
2015-06-03:02:33:20
#连接失败
[root@hzvscmdb log]# more check_oracle_9.9.log
SQL*Plus: Release 11.2.0.2.0 Production on Wed Jun 3 02:33:21 2015 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA
-12520: TNS:listener could not find available handler for requested type of server Enter user-name: SP2-0306: Invalid option. Usage:
CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identi
fier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] Enter user-name: SP2-0306: Invalid option. Usage: CON
N[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identifier
>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] SP2-0157: unable to CONNECT to ORACLE after 3 attempts, e
xiting SQL*Plus
#统计连接失败的数量
[root@hzvscmdb log]# grep 12520 * | wc -l
131
#增大连接数
SQL> alter system set processes = 300 scope = spfile;
System altered.
SQL> shutdown immediate;
SQL> startup;
SQL> show parameter process;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 300
[root@hzvscmdb sql]# ./check_connect_oracle.sh
[root@hzvscmdb sql]# cd log/
[root@hzvscmdb log]# grep 12520 * | wc -l
0