5 Replies Latest reply: Jan 2, 2013 11:36 AM by jihuyao RSS

    huge disk sort causing continuous full of temporary tablespace

    shd
      Hi,

      We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.

      MERGE INTO HDFCMPR.MPR_TB_MPRMASTER
      USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */
      MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF,
      A.ID
      FROM APP_MRCH_DETHOLD A, MSTBL_TEMP_PG B
      WHERE A.HD_APPCODE = AUTH_CODE
      AND A.TERM_SEQ_NO = REF_ID
      AND TXN_CATEGORY ='ACQ'
      and REF_ID IS NOT NULL) TAB1
      ON (TAB1.ID = MPR_TB_MPRMASTER.ID)
      WHEN MATCHED THEN
      UPDATE
      SET MERCHANT_TRACKID = TAB1.MER_TRACKID, TRAN_ID = TAB1.TRANID,
      UPVALUE = TAB1.UCAF
      ;


      Required help