Skip to Content

How to delete particular entry from mxp_provision table

Dear experts,

For one of our requirements, we suppose to delete particular entry/mskey from the mxp_provision table, from IDM side. For that we know mc_reset_provision stored procedure used, but still entry is not deleting from table. for the above SP, it is asking inputs as pisid and presetaudit, am mskey and auditid.

Could you please help, exactly what inputs to give/how to delete particular entry from mxp_provision table.

Thank you in advance.

Regards,

Jay

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Mar 15 at 10:58 PM
    --CLEAR PROVISION QUEUE
    UPDATE mxp_audit SET ProvStatus = 1100 WHERE AUDITID IN (SELECT auditref FROM MXP_Provision where actionid=XXXXX);
    DELETE FROM mxp_provision where actionid=XXXXX;
    Add comment
    10|10000 characters needed characters exceeded

    • Hi Jaya,

      mc_reset_provision take in parameters pIsId and pResetAudit. With this stored procedure you can't specify the MSKEYVALUE you want to remove.

      1. pIsId is the Identity Store Id (if you have only one it should be 1)
      2. pResetAudit is just a flag, if it's <> 0 then it will remove all pending provision.

      Below the source of the stored procedure

      ---------------------------------------------------------------------------------------
      -- mc_reset_provision
      -- This procedure resets all identity store and provisioing data for the given ids identifier.
      -- THERE IS NO UNDO FOR THIS FUNCTION
      ---------------------------------------------------------------------------------------
      prompt mc_reset_provision
      CREATE OR REPLACE PROCEDURE mc_reset_provision(
      	pIsId number,
      	pResetAudit number)
      
      
      is
      
      
      	lBuf VARCHAR2(256);
      	lIdsName VARCHAR2(50);
      	lStatus number(10);
      	lStatusText varchar2(256);
      Begin
      
      
      	BEGIN
      		SELECT IdStoreName INTO lIdsName FROM MXI_IDStores WHERE is_id = pIsId;
      		exception when no_data_found then
      			lBuf := 'Identity Store ID=' || pIsId || ' does not exist';
      			mc_write_syslog (lBuf,'',1,'',0);
      			RETURN;
      	END;
      
      
      
      
      	lBuf := 'Resetting Identity Store provisioning data "' || lIdsName || '" (ID=' || pIsId || ')';
      	mc_write_syslog (lBuf,'',-1,'',0);
      
      
      
      
      	IF pResetAudit <> 0 then
      
      
      		-- MXP_PROVISION
      		delete from mxp_provision WHERE MSKEY IN
      			(SELECT    MXP_Provision.MSKey
      			 FROM      MXI_VALUES,
      			 	   MXI_Attributes,
      			 	   MXP_Provision
      			 WHERE 	   MXI_VALUES.Attr_ID = MXI_Attributes.Attr_ID AND
      				   MXI_VALUES.MSKEY = MXP_Provision.MSKey AND
      				   (MXI_Attributes.AttrName = 'MSKEYVALUE') AND
      				   (MXI_Attributes.IS_ID = pIsId));
      
      
      	 END IF;
      
      
      	lBuf := 'Identity Store provisioning data "' || lIdsName || '" (ID=' || pIsId || ') has been reset';
      	mc_write_syslog (lBuf,'',0,'',0);
      
      
       	COMMIT;
      END;
      
  • Mar 14 at 09:37 AM

    Hi Jaya,

    If you execute the mentioned stored procedure it would delete all the entries in the provisioning queue.

    Can you let us know the reason why you wanted to delete the entry from the mxp_provision table?

    IF the entry is stuck in the queue with help of provision monitor you can identify the problem and proposed solution for it.

    Regards,

    Deva

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Jaya,

      Did you run provision monitor? Kindly refer to this blog and you would be able to identify teh problem and the solution too for that.

      If still it didnt resolve, you have to post here the stuck entry details in the mxp_provision table and also the problem.json file result once you ran provision monitor for that that entry.

      Regards,

      Deva

  • Mar 19 at 02:14 AM

    Hi Nicolas,

    Thank you very much. Then to delete particular entry, we can try only from DB side.

    Regards,

    Jay

    Add comment
    10|10000 characters needed characters exceeded