Tag Archives: RESET the JOB QUEUE PROCESSES

JOB QUEUE PROCESSES parameter in Oracle

JOB QUEUE PROCESSES parameter in Oracle

JOB QUEUE PROCESSES is used for background process in Oracle such as DBMS JOBS or scheduler job is running from this parameter.
If you set the value to zero then no scheduler or DBMS jobs is running in background. all jobs disabled to run.
Note:
Up to 12c, JOB_QUEUE_PROCESSES is set to a value in the range of 0 to 1000.
For 18c, JOB_QUEUE_PROCESSES is set to a value in the range of 0 to 4000.

Check the value of JOB QUEUE PROCESSES

SQL> Show parameter job_queue_processes

NAME                   TYPE     VALUE
---------------------- -------- -------
job_queue_processes    integer  4000

Set the Value of JOB QUEUE PROCESSES
Note: It will remain permanent even on database reboot.

SQL> alter system set job_queue_processes=1000;
System altered.

RESET the value to default value
–For reset need to reboot the DB.

alter system reset job_queue_processes;
System altered.
-- For take effect need to restart the DB.
Shutdown immediate
Startup

Error
If you tried to reset this command 2nd time then you get the error. For make this command effect you need to restart the DB.

SQL> alter system reset job_queue_processes;
System altered.

SQL> alter system reset job_queue_processes;
alter system reset job_queue_processes
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

Solution

Shutdown immediate
Startup

Advertisements