It is possible that the current number of connections on the database has exceeded the maximum value that it can handle. Select COUNT (*) from v$process--current number of connections Select value from v$parameter where name = ' processes '--maximum number of connections allowed by the database To modify the maximum number of connections: alter system SET processes = Scope = SPFile; To restart the database: Shutdown immediate; Startup --See which users are currently using the data SELECT Osuser, a.username,cpu_time/executions/1000000| | ' S ', Sql_fulltext,machine From V$session A, V$sqlarea b where a.sql_address =b.address order by cpu_time/executions Desc; Select COUNT (*) from V$session #连接数 Select COUNT (*) from v$session where status= ' ACTIVE ' #并发连接数
Show parameter Processes #最大连接 alter system SET processes = value scope = spfile; Restart database #修改连接
Sql> Select Count (*) from v$session where status= ' ACTIVE ';
COUNT (*) ---------- 20
Sql> Select Count (*) from v$session;
COUNT (*) ---------- 187
Sql> Show parameter processes;
NAME TYPE VALUE ------------------------------------ ----------- ---------- Aq_tm_processes integer 0 Db_writer_processes Integer 1 Gcs_server_processes integer 0 Job_queue_processes Integer 10 Log_archive_max_processes Integer 2 Processes integer 450 Sql>
Concurrent Finger Active,i Sql> Select COUNT (*) from V$session #连接数 Sql> Select Count (*) from v$session where status= ' ACTIVE ' #并发连接数 Sql> Show parameter Processes #最大连接 Sql> alter system SET processes = value scope = spfile; Restart database #修改连接
UNIX 1 user sessions correspond to an operating system process While Windows embodies the threads The DBA will periodically check the connection to the database to see if the number of sessions established with the database is normal, and if too many connections are established, the resources of the database are consumed. At the same time, some "hang dead" connections may require manual cleanup by the DBA. The following SQL statement lists the sessions established by the current database: Select Sid,serial#,username,program,machine,status From V$session; The output results are: SID serial# USERNAME Program MACHINE STATUS ---- ------- ---------- ----------- --------------- -------- 1 1 ORACLE. EXE WORK3 ACTIVE 2 1 ORACLE. EXE WORK3 ACTIVE 3 1 ORACLE. EXE WORK3 ACTIVE 4 1 ORACLE. EXE WORK3 ACTIVE 5 3 ORACLE. EXE WORK3 ACTIVE 6 1 ORACLE. EXE WORK3 ACTIVE 7 1 ORACLE. EXE WORK3 ACTIVE 8 SYS Sqlplus. EXE Workgroup\\work3 ACTIVE One 5 dbsnmp dbsnmp.exe Workgroup\\work3 INACTIVE which The ID number of the SID conversation (session); The serial number of the serial# session, together with the SID, to uniquely identify a session; USERNAME the user name to establish the session; Program This session is using what tools to connect to the database; Status of the current session, active indicates that the session is performing certain tasks, inactive that the current session is not performing any action; If the DBA is to manually disconnect a session, execute: Alter system kill session \ ' sid,serial#\ ' SQL statement SQL statement is as follows: SELECT username, machine, program, status, COUNT (machine) as Number of connections From V$session GROUP by username, machine, program, status Order by machine; Show results (different on each person's machine) schneider| Workgroud\wangzheng| toad.exe| Active|1 schneider| workgroup\597728aa514f49d|sqlplusw.exe| Inactive|1 | www-q6zmr2oiu9v| ORACLE. exe| Active|8 public| | | Inactive|0 |