I’ll try to write this up further when I make progress, but just wanted somewhere to share testcase. I’m guessing it’s a multi-tenant bug, but if that’s the case I’m a little surprised why no-one else has hit it.
SQL> CREATE TABLE DUMMY AS SELECT * FROM DBA_OBJECTS WHERE 1=0; Table created. SQL> SELECT resource_consumer_group 2 FROM v$session 3 WHERE sid IN (SELECT sid FROM v$mystat); RESOURCE_CONSUMER_GROUP -------------------------------- OTHER_GROUPS SQL> BEGIN 2 DBMS_SCHEDULER.create_job ( 3 job_name => 'RC_JOBA', 4 job_type => 'PLSQL_BLOCK', 5 job_action => 'BEGIN INSERT INTO DUMMY SELECT * FROM DBA_OBJECTS; DELETE FROM DUMMY; COMMIT; END;', 6 enabled => TRUE); 7 END; 8 / PL/SQL procedure successfully completed. SQL> SELECT DISTINCT consumer_group 2 FROM 3 v$active_session_history ash, 4 dba_rsrc_consumer_groups cg 5 WHERE 6 action = 'RC_JOBA' 7 AND ash.consumer_group_id = cg.consumer_group_id; CONSUMER_GROUP -------------------------------------------------------------------------------- SYS_GROUP SQL>
Edit: I guess if you are testing you may need to update the task the job does to ensure it writes something into v$active_session_history, if you’ve got suggestion for better way to do that let me know, I can’t think of one right now.
Update 2018-03-02: Worryingly this doesn’t seem to be simply a reporting error. I updated the current database resource plan such that any SQL estimated to take over 1 second to complete would be cancelled.
Running a bad SQL directly as user caused query to be cancelled as expected, running via dbms_scheduler attempted to execute the bad SQL.
SQL> CREATE OR REPLACE PROCEDURE KILL_ME_PROC AS 2 BEGIN 3 FOR LOOPY IN (SELECT NULL FROM ALL_OBJECTS A1, ALL_OBJECTS A2, ALL_OBJECTS A3, ALL_OBJECTS A3) 4 LOOP 5 NULL; 6 END LOOP; 7 END; 8 / Procedure created. SQL> SQL> EXEC KILL_ME_PROC; BEGIN KILL_ME_PROC; END; * ERROR at line 1: ORA-00040: active time limit exceeded - call aborted ORA-06512: at "C##PATRICKJOLLIFFE.KILL_ME_PROC", line 3 ORA-06512: at line 1 SQL> SELECT resource_consumer_group 2 FROM v$session 3 WHERE sid IN (SELECT sid FROM v$mystat); RESOURCE_CONSUMER_GROUP -------------------------------- OTHER_GROUPS SQL> BEGIN 2 DBMS_SCHEDULER.create_job ( 3 job_name => 'KILL_ME_JOB2', 4 job_type => 'STORED_PROCEDURE', 5 job_action => 'KILL_ME_PROC', 6 enabled => TRUE); 7 END; 8 / PL/SQL procedure successfully completed. SQL> SELECT ERRORS FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE JOB_NAME = 'KILL_ME_JOB2' AND LOG_DATE > SYSDATE-1/24; ERRORS -------------------------------------------------------------------------------- ORA-01652: unable to extend temp segment by 128 in tablespace TEMP ORA-06512: at "C##PATRICKJOLLIFFE.KILL_ME_PROC", line 3 SQL>
Good notice!
This blog post is about two bugs I think.
In a 12.1.0.2 lab environment I have two PDBs: first was created from PDB$SEED and second one was created by plugging in a non-CDB of the same version. Scheduler jobs run as SYS_GROUP in 1st PDB, but in 2nd they run as OTHER_GROUPS. Used Oracle supplied DEFAULT_PLAN in both PDBs and DEFAULT_CDB_PLAN in CDB. The behaviour seems like a new bug.
CANCEL_SQL directive is not working in general for scheduler jobs in 12.1.0.2, unless use_current_session=>true is used. This is bug 27332108.
Cheers!
Wow, thanks for moving us forwards on this, Can’t believe I hit a second bug when investigating the first. Working with Oracle support to try to raise this as a new bug, will update this post with any progress.