Monthly Archives: April 2017

DBMS_APPLICATION_INFO

25 Tuesday Apr 2017

Posted by in Underestimated Features

≈ Leave a comment

Tags

,

Ever asked yourselves

  • What progress has my batch process done so far? 
  • Which iteration is currently processed?
  • How much longer will the job run?
  • What step is the batch process currently processing?

These and other questions could be easily answered if more developers made use of the DBMS_APPLICATION_INFO package. 

DBMS_APPLICATION_INFO may add information to V$SESSION and V$SESSION_LONGOPS views. The package is writing its data to memory and therefore the overhead produced by using this package is insignificant.

 

SET_MODULE

When a batch is started it should subscribe itself using the SET_MODULE procedure of DBMS_APPLICATION_INFO. Along with the name of the module the initial action is registered.

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Module(module_name => 'MyBatch'
                                       ,action_name => 'Initialize');
   ...
END;
/

The name of the module is limited to 48 bytes (longer values will be truncated), the name of the action to 32 bytes.

SET_ACTION

This procedure is used to provide the current state of the module that has been registered using the Set_Module procedure. 

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Action(action_name => 'Closing Orders');
   ...
END;
/

SET_CLIENT_INFO

The “Client Information” may be used to provide additional information.

BEGIN
   SYS.DBMS_APPLICATION_INFO.Set_Action(action_name => 'Closing Orders');
   SYS.DBMS_APPLICATION_INFO.Set_Client_Info(Client_Info => 'Iteration 3');
 ...
END;
/

The client_info is limited to 64 bytes.

Querying V$SESSION View

V$SESSION may be queried from a different session to see what the batch process is doing.

select sid, serial#, username, module, action, client_info
 from v$session
 where username = 'TEST'
/

  SID SERIAL# USERNAME MODULE         ACTION          CLIENT_INFO 
----- ------- -------- -------------- --------------- --------------
   38    6914 TEST     SQL Developer 
  269   51268 TEST     SQL Developer  Closing Orders  Iteration 3

SET_SESSION_LONGOPS

This procedure call is reflected in the V$SESSION_LONGOPS view and may be used to provide information on the progress of a task.

DECLARE
   l_rindex     BINARY_INTEGER;
   l_slno       BINARY_INTEGER;
   l_iterations PLS_INTEGER := 10;
BEGIN
   l_rindex := SYS.DBMS_APPLICATION_INFO.set_session_longops_nohint;

   FOR i in 1..l_iterations
   LOOP
      SYS.DBMS_APPLICATION_INFO.set_session_longops(rindex      => l_rindex
                                                   ,slno        => l_slno
                                                   ,op_name     => 'TEST'
                                                   ,sofar       => i
                                                   ,totalwork   => l_iterations
                                                   ,units       => 'Iterations'
                                                   );
      SYS.DBMS_LOCK.Sleep(3);
   END LOOP;
END;
/

 

Querying the longops view:

SELECT opname
      ,sofar
      ,totalwork
      ,units
  FROM v$session_longops
/

OPNAME       SOFAR TOTALWORK  UNITS 
------- ---------- ---------- --------------------------------
TEST             2         10 Iterations

Conclusion

If you have not yet used DBMS_APPLICATION_INFO in any of your processes it is worth looking at this package. The information provided when using this package for long running processes might keep you away from killing a process just because you *think* it should have ended long time ago but you have no idea how far the process is….

 

ORACLE 12.2 – Enhanced Whitelist (ACCESSIBLE BY)

25 Tuesday Apr 2017

Posted by in Uncategorized

≈ Leave a comment

Tags

, ,

With Version 12.1 ORACLE provided a new way to determine that a program unit may only be called by other program units which are part of a whilelist. This new feature was implemented using the ACCESSIBLE BY clause. ACCESSIBLE BY was declared on top level (e.g. package specification).

ORACLE 12.2 provides a higher granularity for the whitelist. It is now possible to define the access on a packaged procedure/function too.

Code Example:

CREATE OR REPLACE PACKAGE emp_tapi IS
   TYPE emp_tapi_rect IS RECORD (ename    emp.ename%TYPE
                               , comm     emp.comm%TYPE
                               , hiredate emp.hiredate%TYPE
                               , empno    emp.empno%TYPE
                               , mgr      emp.mgr%TYPE
                               , job      emp.job%TYPE
                               , deptno   emp.deptno%TYPE
                               , sal      emp.sal%TYPE);

   TYPE emp_tapi_tabt IS TABLE OF emp_tapi_rect;

-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- update
-- ========================================================================
   PROCEDURE upd (emp_tapi_rec IN emp_tapi_rect) 
      ACCESSIBLE BY (PACKAGE emp_maint_up);

-- ========================================================================
-- delete
-- ========================================================================
   PROCEDURE del (p_empno IN emp.empno%type)
      ACCESSIBLE BY (TRIGGER emp_io_d_t);
END emp_tapi;
/

CREATE OR REPLACE PACKAGE BODY emp_tapi IS
-- ========================================================================
-- insert
-- ========================================================================
   PROCEDURE ins (emp_tapi_rec IN OUT NOCOPY emp_tapi_rect)
      ACCESSIBLE BY (PACKAGE emp_maint_up)
   IS
   BEGIN
      ...

As you can see, ACCESSIBLE BY clause has to be specified in specification and body, if we want to define the whitelist on subprograms. 

Conclusion

The ACCESSIBLE BY clause is very useful (e.g. refactoring) and the higher granularity will help to make it even better.