Do not set job_queue_processes at PDB level in 12.1.0.2

MOS Doc ID 2175443.1 goes into more detail, including the following quote:

It is possible to set job_queue_processes at pdb level on 12.1.0.2 multitenant database, but it does not work correctly.

I have managed to reproduce the actual behavior. Of particular concern is that this allows a local user on one PDB to disrupt things on other PDBs.

My test system is running 12.1.0.2, multi-tenant with two PDBs, PDB1 and PDB2. Each PDB has a local user with DBA privileges, dba1, and dba2 respectively.

[oracle@awrw admin]$ sqlplus dba1/oracle@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:34:41 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 02 2017 13:33:46 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter job_queue_processes

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes		     integer	 1000
SQL> alter system set job_queue_processes=0 container=current;

System altered.

SQL> show parameter job_queue_processes

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes		     integer	 0
SQL>

So dba1 seems to have changed job_queue_processes, in their own PDB, pdb1.

The alert log reveals some cause for concern:

CJQ0 started with pid=34, OS id=31546
Stopping background process CJQ0
Sun Apr 02 13:35:01 2017
ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH;

Sure enough, in PDB2, even though job_queue_processes parameter seems to be set to it’s default value, we are unable to use scheduler:

[oracle@awrw admin]$ sqlplus dba2/oracle@pdb2

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:44:19 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 02 2017 13:43:25 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter job_queue_processes

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes		     integer	 1000
SQL> exec sys.dbms_scheduler.create_job( 'DUMMY_JOB', job_type=>'PLSQL_BLOCK', job_action=>'BEGIN NULL; END;');

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_scheduler.run_job( 'DUMMY_JOB', false);
BEGIN sys.dbms_scheduler.run_job( 'DUMMY_JOB', false); END;

*
ERROR at line 1:
ORA-27492: unable to run job "DBA2"."DUMMY_JOB": scheduler unavailable
ORA-06512: at "SYS.DBMS_ISCHED", line 209
ORA-06512: at "SYS.DBMS_SCHEDULER", line 594
ORA-06512: at line 1

I connect back to PDB1 and reset the parameter:

[oracle@awrw admin]$ sqlplus dba1/oracle@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:46:40 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 02 2017 13:34:41 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system set job_queue_processes=1000 container=current;

System altered.

In the alert log I can see Job Queue Co-ordinator process is restarted:

Starting background process CJQ0
Sun Apr 02 13:47:08 2017
ALTER SYSTEM SET job_queue_processes=1000 SCOPE=BOTH;
Sun Apr 02 13:47:08 2017
CJQ0 started with pid=34, OS id=2227

And sure enough I am now able to run schedule jobs in PDB2.

[oracle@awrw ~]$ sqlplus dba2/oracle@pdb2

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 2 13:48:51 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Apr 02 2017 13:44:19 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exec sys.dbms_scheduler.run_job( 'DUMMY_JOB', false);

PL/SQL procedure successfully completed.