Re: How to stop ALL dbms_jobs from executing ?

"Dave" <x_at_x.com> wrote in message
news:iBRhd.7370$Fu2.4475_at_fe1.news.blueyonder.co.uk...
>
> "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
>>
>>
>
> set job queue process = 0
>
> alter alter database quiesce (i think that stops all transaction)

You'll find the command is "alter database quiesce restricted", and it only works if you are using resource manager, and have been doing so since instance startup without interruption.

The actual answer to the original poster is that he has to do precisely NOTHING to "minimise access to the tables during the [move]". Because moving a table takes an exclusive table lock, and hence the table is protected against all DML without further effort on his part.

The other answer he should have been given is: of course a hot backup is fine. Hot backups wouldn't be much of a backup if they sometimes failed to be of use.

Just move the tables, in other words. And stop worrying.

Regards
HJR Received on Tue Nov 02 2004 - 14:25:52 CST