Oracle processes and sessions parameters (number of process connections & number of session connections)

Oracle processes and sessions Parameters （ Number of process connections & Number of session connections ）

If the configuration is too small , Errors may be reported when connecting ：ORA-12519, TNS:no appropriate service handler found

Oracle The official advice sessions = process * 1.1 + 5

see process and session：
show parameter process;
show parameter session;

View the connection number configuration of the database process ：
show parameter process; 
or
select value from v$parameter where name = 'processes';

Check the number of connections of the current process of the database ：
select count(*) from v$process;

View the configuration parameters of the number of connections of the database session
show parameter session;
or
select value from v$parameter where name = 'sessions'; 

View the number of connections to the current session of the database ：
select count(*) from v$session;

View the number of concurrent connections to the database ：
select count(*) from v$session where status='ACTIVE';

View the session details established by the current database ：
select sid,serial#,username,program,machine,status from v$session;

The default values are as follows ：
SQL> show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     4
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
SQL> 

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
session_cached_cursors               integer     50
session_max_open_files               integer     10
sessions                             integer     256
shared_server_sessions               integer
SQL> 

modify process and session To configure

First check the database spfile still pfile start-up ：
show parameter spfile -- It's worth it spfile

modify process and session
alter system set processes=500 scope=spfile;
alter system set sessions=555 scope=spfile;

Restart and take effect ：
shutdown immediate;
startup;
show parameter process;
show parameter session;

Effective after modification session The number is larger than the setting , The reason is not studied in detail

SQL> show parameter process;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     0
cell_offload_processing              boolean     TRUE
db_writer_processes                  integer     4
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     500
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
session_cached_cursors               integer     50
session_max_open_files               integer     10
sessions                             integer     800
shared_server_sessions               integer
SQL>