The maximum number of connections to view and modify in oracle, and the maximum number to view in oracle

The maximum number of connections to view and modify in oracle, and the maximum number to view in oracle
Step 1: In the cmd command line, enter sqlplus or directly open command window in plsql

Step 2: Enter the user name and password as prompted

1. view the processes and sessions Parameters

SQL> show parameter processes　　NAME                                 TYPE        VALUE　　db_writer_processes                 integer    1　　gcs_server_processes                integer    0　　job_queue_processes                 integer    10　　log_archive_max_processes           integer    2　　processes                           integer    50

SQL> show parameter sessions　　NAME                                 TYPE        VALUE　　license_max_sessions                integer    0　　license_sessions_warning            integer    0　　logmnr_max_persistent_sessions      integer    1　　sessions                            integer    60　　shared_server_sessions              integer

　 2. Modify the values of processes and sessions.　　

SQL> alter system set processes = 300 scope = spfile; the system has been changed. SQL> alter system set sessions = 335 scope = spfile; the system has been changed.

　3. to modify the processes and sessions values, the oracle server must be restarted to take effect.

The number of ORACLE connections (sessions) is related to the number of processes in the parameter file. Their relationships are as follows:
Sessions = (1.1 * process + 5)

Abstract (2)

　 Query the number of connections of the current process in the database:
Select count (*) from v $ process;

　　 View the number of connections of the current session of the database:
Elect count (*) from v $ session;

　　 View the number of concurrent connections of the database:
Select count (*) from v $ session where status = 'active ';

　　 View the Sessions established by the current database:
Select sid, serial #, username, program, machine, status from v $ session;

　 Query the maximum number of connections allowed by the database:
Select value from v $ parameter where name = 'processs ';

Or: show parameter processes;

　　 Modify the maximum number of connections allowed by the database:
Alter system set processses = 300 scope = spfile;

(You need to restart the database to modify the number of connections)


　 Restart the database:

Shutdown immediate;

Startup;


　　 Check which users are 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;


Note: one session in UNIX corresponds to an operating system process, while Windows is reflected in the thread.


Start oracle


Su-oracle


Sqlplus system/pwd as sysdba // enter SQL


Startup // start the database


Lsnrctl start // start the listener


Sqlplus "/as sysdba"


Shutdown immediate;


Startup mount;

Alter database open;

However, when I change the maximum value to 1200, an error is returned in java 356.

How can I check the relationship between the number of connections, number of processes, and number of users in ORACLE?

SQL> select count (*) from v $ session # Number of connections
SQL> Select count (*) from v $ session where status = 'active' # number of concurrent connections
SQL> show parameter processes # maximum connection

Process: this parameter limits the number of operating system processes (or the number of threads in Windows) that can be connected to SGA. The total number must be large enough, this can be applied to background processes and all dedicated server processes. In addition, the number of Shared Server Processes and scheduling processes is also included. therefore, in a dedicated server environment, this is a method to limit the number of concurrent connections.

Sessions: applies to the oracle hierarchy rather than the operating system hierarchy. This parameter limits the number of concurrent logins to a specified instance without considering logon through a dedicated server or shared server.

Oracle always prompts to limit the number of connections. How can I limit the number of connections in oracle?

It can only be set to a large value, not infinite
Current number of connections
Select count (*) from v $ process;
Set the maximum number of connections (default value: 150)
Select value from v $ parameter where name = 'processs ';
Modify the maximum number of connections
Alter system set processses = 300 scope = spfile;
When the maximum number of connections to the database is not enough, the client connection will fail intermittently, And the ORA-12519 is reported.

How to modify the maximum number of connections in Oracle
1. Go to "SQL * Plus Worksheet", as shown in.
2. Click "Change Database Connection ..." As shown in.
3. Enter the "database connection information" interface, enter the user name, password, and name of the database service for which you want to modify the maximum number of connections. Select "SYSDBA" as the connection identity and click "OK", as shown in.
4. Enter the command "show parameter processes;" and run the command to view the current maximum number of connections, as shown in.
5. Run the command "alter system set processes = 600 scope = spfile;" and change the maximum number of connections to 600, as shown in.
6. Enter the command "create pfile from spfile;" and run the command to create a Profile, as shown in.
7. Disable the "Enterprise Manager Console", restart the Oracle service, or restart the Oracle Database Server.
8. Repeat steps 1, 2, 3, and 4 to confirm that the maximum number of connections is successfully modified.