Re: How to stop ALL dbms_jobs from executing ?

"G Dahler" <gordon.dahler_at_spamex.com> wrote in message news:<feRhd.12761$OD3.744251_at_news20.bellglobal.com>...
> Oracle 8174, solaris 2.6
>
> I will be doing some maintenance on a database (moving tables from DMT to
> LMT tablespaces and rebuilding indexes) and I want to minimize access to the
> tables during the operation. Is there a way to stop all jobs scheduled with
> DBMS_JOB from executing, other than flagging all of them as "broken" ?
>
> I would like NOT to have to shut down the database if possible.
>
> Should I take a cold backup before attempting this or would my hot backup
> suffice if something goes very wrong during the "move" of the tables ?
>
> Thanks

Hello!

First check out parameter job_queue_processes: SQL> show parameter job_queue_processes

NAME                                 TYPE    VALUE
------------------------------------ -------
------------------------------
job_queue_processes                  integer 3

Then set them to 0 (doing this you prevent job executing) alter system set job_queue_processes=0;

also prevent user login:
alter system enable restricted session;

(if you need some users to log in at this time, grant them restricted session privilege)

After your job, set back:
alter system disable restricted session; alter system set job_queue_processes=3;

ITUncles Received on Wed Nov 03 2004 - 07:28:57 CST