Re: preferred method to sleep()?

On Nov 12, 4:13 am, Laurenz Albe <inv..._at_spam.to.invalid> wrote:
> m..._at_pixar.com wrote:
> > I see four methods to sleep:
>
> >    * dbms_backup_restore
> >    * dbms_drs
> >    * dbms_lock
> >    * user_lock
>
> > Are any of them preferrable, deprecated, recommended, etc?
>
> Just from looking at the Oracle 11.1 documentation, I would recommend
> DBMS_LOCK.SLEEP, since DBMS_LOCK is the only package in your list
> that is described inhttp://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/toc.htm
>
> The other ones are probably internal and not intended for public use,
> with the drawback that they may change without notice in new releases.
>
> Yours,
> Laurenz Albe

Yes, but dbms_lock.sleep has had a known error in its timing since it was introduced and also apparently for versions 8iR2 (8.1.6) through 11gR1(11.1.0.6) has a bug which can cause a serious error in the time slept for long sleep times.

If you have support the following note may be of interest

Instead of DBMS_LOCK.SLEEP Procedure Use SYS.DBMS_BACKUP_RESTORE.SLEEP For Time Interval > 3600
document #471246.1

For short sleeps where the timing does not have to be exact dbms_lock.sleep works OK.

HTH -- Mark D Powell -- Received on Wed Nov 12 2008 - 10:48:35 CST