ORACLE to view and modify the maximum number of connections

  • 2021-12-04 20:10:13
  • OfStack

Step 1, on the cmd command line, enter sqlplus

Step 2: Enter the user name and password according to the prompt

1. View processes and sessions parameters

SQL > show parameter processes


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


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 processes and sessions values

SQL > alter system set processes=300 scope=spfile;

The system has changed.

SQL > alter system set sessions=335 scope=spfile;

The system has changed.

3. Modifying processes and sessions values requires restarting the oracle server to take effect

The number of connections for ORACLE (sessions) is related to the number of processes in its parameter file (process), and their relationship is as follows:

sessions= (1.1*process+5)

Pick (2)

Query the number of connections in the current process of the database:

select count (*) from v $process;

View the number of connections for the current session of the database:

elect count (*) from v $session;

View the number of concurrent connections to the database:

select count (*) from v $session where status = 'ACTIVE';

View the current database established sessions:

select sid, serial #, username, program, machine, status from v $session;

Maximum number of connections allowed to query the database:

select value from v $parameter where name = 'processes';

Or: show parameter processes;

Modify the maximum number of connections allowed in the database:

alter system set processes = 300 scope = spfile;

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

Restart the database:

shutdown immediate;


See which users are currently using 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: UNIX One user session corresponds to one operating system process, while Windows is embodied in threads.

Start oracle

su - oracle

    sqlplus system/pwd as sysdba // Enter sql

    startup                   // Start the database 

    lsnrctl start               // Start listening 

    sqlplus "/as sysdba"

    shutdown immediate;

    startup mount;

    alter database open;

Related articles: