Shulou(Shulou.com)05/31 Report--

What this article shares with you is about how to analyze and solve ora-12516 errors. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

An ORA-12516 error occurred when using swingbench to stress test the oracle database. This error is usually due to the fact that the number of sessions in oracle exceeds the limit, and it is usually caused by multiple connect establishing multiple connection sessions, resulting in oracle being unable to respond to new requests, resulting in ora-12516 errors.

The ideas and methods to solve the problem:

First check to see if the number of existing processes in the database has reached the size of the parameter processes.

Select count (*) from v$process

Gets the current number of processes in the database.

Select value from v$parameter where name = 'processes'

Get the upper limit on the number of processes.

Looking at the current sessions value, it is found that the number of session is very close to the value of the processes parameter.

SQL > conn / as sysdba

Connected.

SQL > select count (*) from v$session

one hundred and seventy

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 150

SQL > show parameter sessions

NAME TYPE VALUE

Java_soft_sessionspace_limit integer 0l

Icense_max_sessions integer 0

License_sessions_warning integer 0

Logmnr_max_persistent_sessions integer 1

Sessions integer 175

Shared_server_sessions integer

Then modify the processes and sessions values

SQL > alter system set processes=500 scope=spfile

The system has changed.

SQL > alter system set sessions=555 scope=spfile

The system has changed.

Note: as long as the number of session connections exceeds the above process number 150 or sessions number 170, another session process will generate a 12516 error. So you can modify the value:

Sessions=1.1*processes+5;// this is the relationship between the sessions value and the processes value, and it is best to do so

So if you want to set the number of processes to 500, the number of sessions must be 1.1 "500" 555.

View the processes and sessions parameters, but the changes do not take effect

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 150

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 170

Shared_server_sessions integer

Restart the database for the changes to take effect

SQL > shutdown immediate

SQL > startup

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 500

SQL > show parameter sessions

NAME TYPE VALUE

Java_soft_sessionspace_limit integer 0

License_max_sessions integer 0

License_sessions_warning integer 0

Logmnr_max_persistent_sessions integer 1

Sessions integer 555

Shared_server_sessions integer

The above is how to analyze and solve ora-12516 errors. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.