ORA-24093: AQ agent SCHED$_% not granted privileges of database user %

These days, when trying to add dbms_scheduler notifications for different user I was getting this strange error:

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'APPOWNER.MYJOB',
  recipients =>  'me@company.com',
  events     =>  'job_started, job_succeeded'
  );
END;
/

ORA-24093: AQ agent SCHED$_XXX not granted privileges of database user APPUSER
ORA-06512: at "SYS.DBMS_ISCHED", line 8296
ORA-06512: at "SYS.DBMS_SCHEDULER", line 4353
ORA-06512: at line 3

The issue is that “secure queue access must be granted to an Oracle Database Advanced Queuing (AQ) agent explicitly for both enqueue and dequeue operations. You grant the agent these privileges using the ENABLE_DB_ACCESS procedure in the DBMS_AQADM package” (http://docs.oracle.com/database/121/STRMS/strms_trprop.htm#STRMS1046)

So, problem solved with this:

BEGIN
  DBMS_AQADM.ENABLE_DB_ACCESS(
    agent_name  => 'SCHED$_XXX',
    db_username => 'APPUSER');
END;
/

See you next week!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.