Check the maximum number of connections allowed by the oracle database and the current number of connections.
Step 1: On the cmd command line, enter sqlplus
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.
In Oracle, there is a thing called spfile, that is, the dynamic parameter file, which sets various Oracle parameters. The so-called dynamics mean that you can change database parameters without shutting down the database and record them in spfile. There are four scope options when changing parameters. Scope is the range
++ Scope = spfile only changes the records in the spfile, and does not change the memory, that is, it does not take effect immediately, but waits for the next database to take effect. Some parameters can only be changed in this way
++ Scope = memory only changes the memory and does not change the spfile. That is, it will become invalid upon next startup.
++ Scope = both memory and spfile are changed
++ Does not specify the scope parameter, which is equivalent to scope = both.
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;
------------------------------------------------------------------------
Select sum (pga_used_mem)/1024 total_used, sum (pga_used_mem)/count (1)/1024 used_avg, sum (pga_alloc_mem)/1024 total_alloc, sum (FIG)/count (1) /1024 alloc_avg from v $ process;
TOTAL_USED USED_AVG TOTAL_ALLOC ALLOC_AVG
-----------------------------------------
60971.5898 1847.62393 142401.8125 4315.20643
We can see that about one allocation is about 5 MB, and each allocation is 2 MB.