Oracle 实战篇 --- 最大连接数

为什么80%的码农都做不了架构师？>>>

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

转载于:https://my.oschina.net/wangbinbin0326/blog/424338