ORA-12516錯誤解決

ORA-12516問題的原因有很多，這裡說一下最常見的一種。由於我們做行業的大多為基於資料庫程式設計。尤其是進行壓力測試的時候，這個問題就更加常見了。但這個問題出現了，恭喜你，因為這很容易解決

Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\ibmsz>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 16 16:13:19 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1248552 bytes
Variable Size             100664024 bytes
Database Buffers          176160768 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL> show parameter processes  

ps|ef  grep xx   在配置檔案中過濾

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     400
SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
session_cached_cursors               integer     20
session_max_open_files               integer     10
sessions                             integer     445
shared_server_sessions               integer
SQL> alter system set processes = 300 scope = spfile;  ---三種狀態中的一種，需要重新啟動，引數生效
System altered.

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     400

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

Total System Global Area  285212672 bytes
Fixed Size                  1248552 bytes
Variable Size             100664024 bytes
Database Buffers          176160768 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
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     300

SQL> show parameter sessions;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
java_max_sessionspace_size           integer     0
java_soft_sessionspace_limit         integer     0
license_max_sessions                 integer     0
license_sessions_warning             integer     0
logmnr_max_persistent_sessions       integer     1
sessions                             integer     335
shared_server_sessions               integer
SQL> show parameter open_cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

註釋：sessions = processes*1.1 + 5

SQL> alter system set open_cursors=400 scope=both;   ---不需要重新啟動，引數即可生效

System altered.

SQL> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     400
SQL>