-- USER
DROP USER hajo CASCADE;
CREATE USER hajo IDENTIFIED BY ***** DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp account unlock;

-- QUOTAS
ALTER USER hajo QUOTA UNLIMITED ON users;

-- GRANTS
GRANT RESOURCE TO hajo;
GRANT CREATE SESSION TO hajo;
GRANT CREATE PROCEDURE TO hajo;
/*____________________________________________________________________________________________________*/

CONNECT hajo/*****
/*______________________________________________________________________________________________________

   Initial Package Creation
______________________________________________________________________________________________________*/

CREATE OR REPLACE PACKAGE hajo.pkg_sleeper
AS
  PROCEDURE p_sleep;
END pkg_sleeper;
/
CREATE OR REPLACE PACKAGE BODY hajo.pkg_sleeper
AS
  PROCEDURE p_sleep
  IS
    v_time_in_secs INT  := 15; --number of seconds
    v_now          DATE;
  BEGIN
    SELECT SYSDATE INTO v_now FROM DUAL;
    DBMS_OUTPUT.PUT_LINE ('Start: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
    LOOP
      EXIT
    WHEN v_now + (v_time_in_secs * (1/86400)) <= SYSDATE;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ('End..: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
  END p_sleep;
END pkg_sleeper;
/
/*______________________________________________________________________________________________________

   (Other session!) Run the Procedure twice with a Lap Time...
______________________________________________________________________________________________________*/

set serveroutput on
exec hajo.pkg_sleeper.p_sleep;
exec DBMS_OUTPUT.PUT_LINE ('Lap..: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
exec hajo.pkg_sleeper.p_sleep;
/*______________________________________________________________________________________________________

   ...while this Session tries to recompile
______________________________________________________________________________________________________*/

set serveroutput on
exec DBMS_OUTPUT.PUT_LINE ('Comp Start: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
set serveroutput off

CREATE OR REPLACE PACKAGE BODY hajo.pkg_sleeper
AS
  PROCEDURE p_sleep
  IS
    v_time_in_secs INT  := 5; --number of seconds
    v_now          DATE;
  BEGIN
    SELECT SYSDATE INTO v_now FROM DUAL;
    DBMS_OUTPUT.PUT_LINE ('Start: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
    LOOP
      EXIT
    WHEN v_now + (v_time_in_secs * (1/86400)) <= SYSDATE;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE ('End..: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
  END p_sleep;
END pkg_sleeper;
/

set serveroutput on
exec DBMS_OUTPUT.PUT_LINE ('Comp End..: ' || TO_CHAR(SYSDATE,'YYYY-MM-DD_HH24:MI:SS'));
set serveroutput off