Oracle Database Discussions

Hi,

I have a procedure which is being used to update more than 4 millions records and it is taking 5 hours to complete so now my client is asking me that how do we know that script is running or not.

So i put some DBMS_OUTPUT in the procedure but that show only after the procedure get executed not in between the execution of the script.

But i believe oracle must have some kind functionality for that that we can know the status of the running procedure   like this much records have been updated or something else informational...

CREATE OR REPLACE PROCEDURE PPV_CREDIT_LIMIT(p_exid NUMBER)

IS

   TYPE tab_svc_id               IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

   rs_svc_id                     tab_svc_id;

   c_class_SubSvcSpec                constant pls_integer := GET_CLASS_ID('SPEC');

   c_svc_smp_cpe_cas                 constant pls_integer := GET_SVCID('CAS');

   c_parm_net_ppv_credit_limit       constant pls_integer := GET_PARM_ID('C_LIMIT');

   c_deleted                         constant pls_integer := FN_GET_STATUS_ID('deleted');

   c_inactive                        constant pls_integer := FN_GET_STATUS_ID('inactive');

   c_add_in_progress                 constant pls_integer := FN_GET_STATUS_ID('add_in_progress');

   c_activation_in_progress          constant pls_integer := FN_GET_STATUS_ID('activation_in_progress');

   c_courtesy_block_in_progress      constant pls_integer := FN_GET_STATUS_ID('courtesy_block_in_progress');

   c_mso_block_in_progress           constant pls_integer := FN_GET_STATUS_ID('mso_block_in_progress');

   c_delete_in_progress              constant pls_integer := FN_GET_STATUS_ID('delete_in_progress');

   c_deactivation_in_progress        constant pls_integer := FN_GET_STATUS_ID('deactivation_in_progress');

   c_change_in_progress              constant pls_integer := FN_GET_STATUS_ID('change_in_progress');

   c_ppv_credit_limit                constant varchar2(6) := '140.00';

   -- Added for net_creditthreshold parm

   c_parm_ppv_credit_threshold       constant pls_integer := GET_PARM_ID('net_ppv_creditthreshold');

   c_ppv_credit_threshold            constant varchar2(6) := '80.00';

   ilimit                            CONSTANT PLS_INTEGER := 1000;

   iCheck                            CONSTANT PLS_INTEGER := 10;

   l_total_recs                               PLS_INTEGER;

   l_rec_cnt                                  PLS_INTEGER;

   l_curr_cnt                                 PLS_INTEGER := 0;

   l_batch                                    PLS_INTEGER := 0;

   v_stop_flag                                CHAR(1) := 'N';

   cursor curPPV_CL IS

          select ss.sub_svc_id

            from sub_svc ss,

                 sub_svc_parm ssp

           where ss.sub_svc_status_id not in (c_deleted,

                                              c_inactive,

                                              c_add_in_progress,

                                              c_activation_in_progress,

                                              c_courtesy_block_in_progress,

                                              c_mso_block_in_progress,

                                              c_delete_in_progress,

                                              c_deactivation_in_progress,

                                              c_change_in_progress)

             and ssp.parm_id = c_parm_net_ppv_credit_limit

             and ssp.val <> '140.00'

             AND SS.SUB_SVC_ID = SSP.SUB_SVC_ID

             and rownum < 2001;

BEGIN

   DBMS_APPLICATION_INFO.set_action (NULL);

   DBMS_APPLICATION_INFO.set_module (NULL, NULL);

   DBMS_APPLICATION_INFO.set_client_info (NULL);

   DBMS_APPLICATION_INFO.set_module (module_name => 'Procedure: PPV_CREDIT_LIMIT',

                                     action_name => 'Counting total updatable records');

   OPEN curPPV_CL;

   LOOP

       FETCH curPPV_CL

        BULK COLLECT INTO rs_svc_id limit ilimit;

       l_rec_cnt := rs_svc_id.COUNT;

       l_curr_cnt := l_curr_cnt + l_rec_cnt;

       DBMS_APPLICATION_INFO.set_module (module_name => 'Procedure: PPV_CREDIT_LIMIT',

                                         action_name => 'Updating ' || l_curr_cnt );

       forall idx in 1 .. l_rec_cnt

          UPDATE SUB_SVC_PARM

             SET VAL = c_ppv_credit_limit

           WHERE SUB_SVC_ID = rs_svc_id(idx)

             AND PARM_ID = c_parm_net_ppv_credit_limit;

       forall idx in 1 .. l_rec_cnt 

          UPDATE SUB_SVC_PARM

             SET VAL = c_ppv_credit_threshold

           WHERE SUB_SVC_ID = rs_svc_id(idx)

             AND PARM_ID = c_parm_ppv_credit_threshold;

  COMMIT;

       l_batch := l_batch + 1;

       DBMS_APPLICATION_INFO.set_client_info ('BATCH:' || l_batch * ilimit);

       EXIT WHEN l_rec_cnt < ilimit;

       IF   MOD(l_batch, iCheck) = 0

       THEN

            SELECT STOP_FLAG

              INTO v_stop_flag

              FROM DM_PPV_CREDIT_LIMIT

             WHERE EXECUTION_ID = p_exid;

       END IF;

       EXIT WHEN v_stop_flag = 'Y';

   END LOOP;

   CLOSE curPPV_CL;

DBMS_OUTPUT.PUT_LINE('Have updated records : ' || l_curr_cnt );

EXCEPTION

    WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);

END PPV_CREDIT_LIMIT;

This is the procedure which is taking almost 5 hours to run on 2M set of data as there are two update statements so it becomes 4M.

In this procedure DBMS_APPLICATION_INFO is being used can this help me getting information while the script is still running

I am using DB -- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production