I need to stop the execution of all the refresh group registered at my database, this is because I need to run a database maintenance process and I don't want any other job running. How Can I do this?
I try this but it fail because the job need to be running.
SQL> exec sys.dbms_job.broken (53,true);
BEGIN sys.dbms_job.broken (53,true); END;
ERROR at line 1:
ORA-23421: job number 53 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 770
ORA-06512: at "SYS.DBMS_JOB", line 254
ORA-06512: at line 1
This error is caused when you try to change job you are not an owner. Look to dba_jobs who is owner (log_user). And then login as this user and set job as broken.
Or you can use "internal" ORACLE package dbms_ijob to manipulate any job.