Let your DB session sleep without DBMS_LOCK!
Recht häufig braucht man auch in der SQL- und PL/SQL Programmierung eine "SLEEP"-Funktion, also
eine Funktion, die einfach nur eine bestimmte Anzahl Sekunden wartet und gar nichts tut. In der
Datenbank ist eine solche auch vorhanden. Im Package DBMS_LOCK gibt es die Prozedur SLEEP ...
SQL> desc dbms_lock
:
PROCEDURE SLEEP
Argument Name Typ In/Out Defaultwert?
------------------------------ ----------------------- ------ --------
SECONDS NUMBER IN
So weit - so gut. Für den DBA.
Für den Entwickler ist es nicht so einfach - denn DBMS_LOCK gehört nun nicht zu den
Paketen, auf die man normalerweise Zugriff hat. Ein GRANT von DBMS_LOCK an PUBLIC wird
allerdings kaum ein DBA machen - aus guten Gründen,
denn die anderen Funktionen in DBMS_LOCK
sollten schon mit Vorsicht eingesetzt werden.
Es gibt aber Alternativen. Wenn APEX in der Datenbank installiert ist, steht im Paket APEX_UTIL
(welches jeder APEX Entwickler kennt), die Prozedur PAUSE zur Verfügung. Diese arbeitet genauso
wie DBMS_LOCK.SLEEP, mit einer Ausnahme: Sie wartet maximal 2 Minuten (120 Sekunden), wie man
an der nicht gewrappten Package Spec sehen kann.
procedure pause (
--
-- Pause for number of seconds identified by p_seconds
-- (capped at 120 seconds)
--
p_seconds in number)
;
Da APEX_UTIL an
PUBLIC gegranted ist, kann auch jeder andere Datenbankuser APEX_UTIL.PAUSE nutzen.
Datenbanken, in denen APEX nicht installiert ist, haben kein APEX_UTIL. Allerdings könnte
man sich (als DBA) APEX zum Vorbild nehmen und eine allgemein nutzbare SLEEP-Funktion
bereitstellen. Da man die Datenbankprozesse unter Kontrolle behalten möchte, ist eine
maximale Wartezeit (wie APEX es macht) eine gute Idee.
Die eigene SLEEP-Funktion könnte also so aussehen:
create or replace procedure global_sleep(p_seconds in number) is
begin
if p_seconds > 300 or p_seconds < 0 then
raise_application_error(-20000, 'SLEEP IS ONLY ALLOWED BETWEEN 1 SECOND AND 5 MINUTES');
else
dbms_lock.sleep(p_seconds);
end if;
end global_sleep;
/
grant execute on global_sleep to public
/
create public synonym global_sleep for sys.global_sleep
/
Das Ergebnis ist eine SLEEP-Funktion, die jeder nutzen kann, ohne dass das Paket DBMS_LOCK freigegeben wird.
Das Maximum liegt im Beispiel bei 5 Minuten - aber das kann ja jeder DBA so einstellen, wie er möchte.
Ich würde diesen Ansatz sehr empfehlen - denn so bleiben die SLEEPs in einer kontrollierten
Umgebung, mit mit DBA_DEPENDENCIES kann der DBA auch jederzeit nachprüfen, wo das genutzt wird.
Das ist aber noch nicht alles: Dem Entwickler bleibt, wenn der Zugriff auf DBMS_LOCK fehlt, APEX
nicht installiert ist und der DBA keine GLOBAL_SLEEP Funktion bereitgestellt hat, noch eine dritte
Möglichkeit: Nämlich mit Hilfe von Java in der Datenbank.
Java kennt die Methode sleep() in der Klasse java.lang.Thread. Da Java in der Datenbank
niemals multithreaded abläuft, sondern alle Threads stets serialisiert werden, wirkt ein
java.lang.Thread.sleep() genauso wie DBMS_LOCK.SLEEP(). Und die Nutzung ist sehr einfach - außer einem CREATE PROCEDURE sind keine besonderen
Privilegien nötig.
create or replace procedure my_java_sleep (
p_milli_seconds in number
) as language java name 'java.lang.Thread.sleep(long)';
/
SQL> exec my_java_sleep(5000);
PL/SQL-Prozedur erfolgreich abgeschlossen.
Abgelaufen: 00:00:05.00
SQL>
Wie man sieht: Es gibt einige Möglichkeiten für eine SLEEP-Funktion - Zugriff auf das
mächtige DBMS_LOCK ist dafür (im Gegensatz zur landläufigen Meinung) gar nicht nötig.
Sometimes a developer needs a "sleep" function - this is not only true in Java or C/C++, but also
in SQL and PL/SQL programming environments. Looking into the Oracle documentation one will quickly
find the procedure SLEEP in the DBMS_LOCK package.
SQL> desc dbms_lock
:
PROCEDURE SLEEP
Argument Name Typ In/Out Defaultwert?
------------------------------ ----------------------- ------ --------
SECONDS NUMBER IN
So far - so good. If you have DBA privileges.
But most developers do not have these. And DBMS_LOCK is a package which should not
be granted to everyone - it's far too mighty. So a developer would at least have
a discussion with his DBA before getting access to DBMS_LOCK. But there are some
alternatives ...
When APEX has been installed into the database there is the package APEX_UTIL.
It
contains a procedure PAUSE which does exactly the same as DBMS_LOCK.SLEEP with
one exception: The sleeping time is being capped at 120 seconds, which we can see by
examining the package spec of APEX_UTIL.
procedure pause (
--
-- Pause for number of seconds identified by p_seconds
-- (capped at 120 seconds)
--
p_seconds in number)
;
Since APEX_UTIL is a public package it can be used
without the need for special privileges. If APEX is not present in the database I'd recommend
to take this appraoch as a template: The DBA could build an own public SLEEP funktion and
make this available to all developers. Here is an example implemention:
create or replace procedure global_sleep(p_seconds in number) is
begin
if p_seconds > 300 or p_seconds < 0 then
raise_application_error(-20000, 'SLEEP IS ONLY ALLOWED BETWEEN 1 SECOND AND 5 MINUTES');
else
dbms_lock.sleep(p_seconds);
end if;
end global_sleep;
/
grant execute on global_sleep to public
/
create public synonym global_sleep for sys.global_sleep
/
As a result, we have a special sleep function which is available to everyone in the
database. Of course, it would also be possible to grant execution privilege to a special
developer role instead of PUBLIC. The limits (here: 5 minutes) and the violation rule (APEX: quiet capping, here: raise an exception) can
be implemented according to individual needs.
Personally I'd strongly recommend an approach like this: The DBA keeps control, since it is his own
procedure. He can control its behaviour and he can (via DBA_DEPENDENCIES) always see where is is being used.
But that's not all - we have another alternative: Imagine, we have no access to DBMS_LOCK, there is no "global" SLEEP
funktion as described above, APEX is not installed and we need a sleep funcktion.
The solution: Use Java in the Database.
Java has the method sleep() within the class java.lang.Thread. Java in the database
supports the complete multithreading API (there is no "real" multithreading, since all threads are
being serialized).
Therefore we can use java.lang.Thread.sleep() to let the database
session sleep for the given amount of milliseconds. Summarized: Thread.sleep() does the same
as DBMS_LOCK.SLEEP. But opposed to that, no special privileges are needed and
every database user with the CREATE PROCEDURE privilege can use it.
create or replace procedure my_java_sleep (
p_milli_seconds in number
) as language java name 'java.lang.Thread.sleep(long)';
/
SQL> exec my_java_sleep(5000);
PL/SQL procedure successfully completed.
Abgelaufen: 00:00:05.00
SQL>
So - you don't really need special privileges in order to let your database session sleep for
a while. But having one global procedure (similar to APEX) is most probably the best approach.