The following example demonstrates how to use the MAX_MINUTES parameter, of the IN_DB_UTIL framework, to define an end time that can be evaluated when looping through multiple operations.
Note that any operation that is started will run to completion, but new operations will not be started if the specified number of minutes has elapsed.
This should only be used for situations where it is acceptable that any remaining operations are not executed if time has expired.
While the example below is executing, you can monitor progress by running the following command, from a separate session, to view the scripted updates to the START_TIME and SUBOBJECT attributes for the IN_DB_UTIL identifier. Any of the other supported attributes can also be updated as needed.
SET SERVEROUTPUT ON;
EXEC IN_DB_UTIL.IN_CONTEXT_PRINT('IN_DB_UTIL');
The following example will run for one minute (MAX_MINUTES=1) and will update the START TIME and SUBOBJECT attributes of the IN_DB_UTIL identifier every ten seconds until time has expired. The output will be displayed only after MAX_MINUTES has elapsed, and the PL/SQL has completed.
SET TIMING ON;
SET SERVEROUTPUT ON;
DECLARE
V_COUNTER NUMBER := 0;
V_CHECK_TIME NUMBER := 0;
V_MAX_MINUTES NUMBER := 1;
V_OPERATION VARCHAR2(128) := 'TEST';
V_IDENTIFIER VARCHAR2(128) := 'IN_DB_UTIL';
V_TAG VARCHAR2(10) := DBMS_RANDOM.STRING('X',10);
BEGIN
IN_DB_UTIL.IN_SESSION_SET(V_IDENTIFIER, V_TAG, V_OPERATION);
IN_DB_UTIL.IN_CONTEXT_SET('OBJECT', V_OPERATION, V_IDENTIFIER);
IN_DB_UTIL.IN_CONTEXT_SET('MAX_MINUTES', V_MAX_MINUTES, V_IDENTIFIER);
IN_DB_UTIL.IN_CALLSTACK_PRINT(V_IDENTIFIER);
IN_DB_UTIL.IN_CONTEXT_PRINT(V_IDENTIFIER);
DBMS_OUTPUT.PUT_LINE(CHR(10));
WHILE V_CHECK_TIME = 0
LOOP
V_COUNTER := V_COUNTER+1;
IN_DB_UTIL.IN_CONTEXT_SET('SUBOBJECT', 'PASS# '||V_COUNTER, V_IDENTIFIER);
IN_DB_UTIL.IN_CONTEXT_SET('STATUS', 'EXECUTING', V_IDENTIFIER);
DBMS_SESSION.SLEEP(10);
SELECT INUSER.IN_DB_UTIL.CHECK_TIME(V_IDENTIFIER) INTO V_CHECK_TIME FROM DUAL;
IF V_CHECK_TIME = 0 THEN
DBMS_OUTPUT.PUT_LINE('MAX_TIME has not elapsed.');
ELSIF V_CHECK_TIME = 1 THEN
DBMS_OUTPUT.PUT_LINE('MAX_TIME has elapsed.');
END IF;
END LOOP;
IN_DB_UTIL.IN_SESSION_CLEAR(V_IDENTIFIER, V_TAG);
IN_DB_UTIL.IN_CALLSTACK_PRINT(V_IDENTIFIER);
IN_DB_UTIL.IN_CONTEXT_PRINT(V_IDENTIFIER);
END;
/
Example Output
Current DB Time: 2022-08-22 17:44:23.937000 Current UTC Time: 2022-08-22 22:44:23.937000 Current Activity for the IN_DB_UTIL identifier: IN_DB_UTIL TAG: N1Q5DYNBN9 IN_DB_UTIL STATUS: EXECUTING IN_DB_UTIL OPERATION: TEST IN_DB_UTIL CALLED BY: NULL IN_DB_UTIL START TIME: 2022-08-22 17:44:23.937000 IN_DB_UTIL MAX TIME: 2022-08-22 17:45:23.937000 IN_DB_UTIL MAX MINUTES: 1 IN_DB_UTIL OBJECT: NULL IN_DB_UTIL SUBOBJECT: NULL IN_DB_UTIL TABLESPACE: NULL IN_DB_UTIL BATCH SIZE: NULL IN_DB_UTIL BULK LIMIT: NULL IN_DB_UTIL PARALLEL: NULL IN_DB_UTIL LOGGING: NULL IN_DB_UTIL OS USER: LOCAL_DOMAIN\USER_NAME IN_DB_UTIL DB_SESSION: 10,60461 MAX_TIME has not elapsed. MAX_TIME has not elapsed. MAX_TIME has not elapsed. MAX_TIME has not elapsed. MAX_TIME has not elapsed. MAX_TIME has elapsed. Current DB Time: 2022-08-22 17:45:23.990000 Current UTC Time: 2022-08-22 22:45:23.990000 Current Activity for the IN_DB_UTIL identifier: IN_DB_UTIL TAG: NULL IN_DB_UTIL STATUS: IDLE IN_DB_UTIL OPERATION: NULL IN_DB_UTIL CALLED BY: NULL IN_DB_UTIL START TIME: NULL IN_DB_UTIL MAX TIME: NULL IN_DB_UTIL MAX MINUTES: NULL IN_DB_UTIL OBJECT: NULL IN_DB_UTIL SUBOBJECT: NULL IN_DB_UTIL TABLESPACE: NULL IN_DB_UTIL BATCH SIZE: NULL IN_DB_UTIL BULK LIMIT: NULL IN_DB_UTIL PARALLEL: NULL IN_DB_UTIL LOGGING: NULL IN_DB_UTIL OS USER: NULL IN_DB_UTIL DB_SESSION: NULL PL/SQL procedure successfully completed. Elapsed: 00:01:00.08