Use DBMS_SESSION.SLEEP instead of DBMS_LOCK.SLEEP on Oracle 18c and above

[maxpsq]

While running snapper, an error may be raised if grants on executing DBMS_LOCK are missing and DBAs do not agree to provide such grants.

ERROR at line 904:
ORA-06550: line 1672, column 10:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1672, column 10:
PL/SQL: Statement ignored
ORA-06550: line 1675, column 6:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 1675, column 6:
PL/SQL: Statement ignored

I suggest to create an internal SLEEP() procedure as a wrapper for the conditional execution of either DBMS_SESSION.SLEEP or DBMS_LOCK.SLEEP, depending on the underlying Oracle version.

[tanelpoder]

Yes, good point. Time to change this (before that, people could have created a separate package via utlsleep.sql).

I don't have a 12.2 database available right now - if you have one, can you check if DBMS_SESSION.SLEEP is present in 12.2 too? (sometimes they introduce these things a version before documenting 'em)

[tanelpoder]

Ok I just pushed snapper v4.34 with the change. Tested on my 19c instance, but not on 12c or 11. Can you run a couple of tests and see if it works for you too?

[maxpsq]

I've just run the tests on 11.2, 12.1, 12.2, 19c and it all worked like a charm. I confirm 12.2 does not have dbms_session.sleep().

Furthermore, I found out dbms_lock.sleep is used in other files too. It may be the case to extend the change there too. Here's the full list:

$  find snapper/ -type f -name '*.sql' -exec grep -iH 'dbms_lock\.sleep' {} \; 
snapper/snapper.sql:    case when substr(banner, instr(banner, 'Release ')+8,2) >= '18' then 'dbms_session.sleep' else 'dbms_lock.sleep' end snapper_howtosleep,
snapper/snapper3.sql:            -- dbms_lock.sleep( (&snapper_sleep - (sysdate - d1)) );
snapper/snapper3.sql:            -- dbms_lock.sleep( (&snapper_sleep - (sysdate - d1))*1000/1024 );
snapper/snapper3.sql:               dbms_lock.sleep( greatest(0.1,(least(1,&snapper_sleep*&snapper_count/100))) );
snapper/snapper3.sql:            dbms_lock.sleep( ((ash_date1+(&snapper_sleep/86400)) - sysdate)*86400 );
snapper/snapper_v1.sql:        dbms_lock.sleep( (&snapper_sleep - (sysdate - d1)) );
snapper/snapper_v1.sql:        -- dbms_lock.sleep( (&snapper_sleep - (sysdate - d1))*1000/1024 );
snapper/snapper_v2.sql:        dbms_lock.sleep( (&snapper_sleep - (sysdate - d1)) );
snapper/snapper_v2.sql:        -- dbms_lock.sleep( (&snapper_sleep - (sysdate - d1))*1000/1024 );
snapper/snapsys.sql:        DBMS_LOCK.SLEEP(dur);

[tanelpoder]

Thanks for testing @maxpsq! I can announce the new version now :-) I'll keep the old versions of snapper.sql as they are, as they are no-longer-updated legacy Snapper versions that still work on old Oracle versions (that don't have dbms_session.sleep anyway).