I am on Oracle 11g R2. I am trying to drop a scheduler chain which belongs to a different user and I get the insufficient privileges error. I check the documentation (https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse009.htm ) and it says that to drop a chain belonging to a different user, the user should have
The user trying to drop the chain has these privileges but is still not able to drop the chain.
USER_B is the chain owner. USER_A is trying to drop it.
SQL> select * from v$version
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL> select user from dual
USER
------------------------------
USER_A
SQL> select * from session_privs
where privilege in ('MANAGE SCHEDULER','CREATE ANY JOB','DROP ANY EVALUATION CONTEXT','DROP ANY RULE SET')
PRIVILEGE
----------------------------------------
DROP ANY EVALUATION CONTEXT
DROP ANY RULE SET
CREATE ANY JOB
MANAGE SCHEDULER
SQL> select owner, object_name, object_type, status from dba_objects
where owner = 'USER_B'
and object_name = 'TEST_CHAIN'
OWNER OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------
USER_B TEST_CHAIN CHAIN VALID
SQL> exec dbms_scheduler.drop_chain('USER_B.TEST_CHAIN',TRUE)
>> BEGIN dbms_scheduler.drop_chain('USER_B.TEST_CHAIN',TRUE); END;
Error at line 16
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 4966
ORA-06512: at "SYS.DBMS_ISCHED", line 1219
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1854
ORA-06512: at line 1