-1

I tried executing the below query but getting error "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP" in Prod where as it is succefully executing in lower environments. Other than increasing the TEMP table space , can someone please suggest an alternative?

Thank you for your help.

INSERT /*+ APPEND */ INTO PFE_GP.CONT_DATA(SC_ID,
        ID,
        PRD,
        CONT,
        QTY,
        PRICE,
        PRICE2,
        PRICE3,
        TOTAL_SALES,
        TOTAL_DISCOUNT)
    SELECT A.*,
            SUM (SALES) OVER (PARTITION BY CONT) AS TOTAL_SALES,
            SUM (DISCOUNT) OVER (PARTITION BY CONT) AS TOTAL_DISCOUNT
    FROM (
            SELECT /*+ FULL(T) PARALLEL(T 8)*/ D.SC_ID,
                T.ID,
                T.PRD,
                R1.CONT,
                T.QTY,
                T.PRICE,
                B.PRICE2,
                B.PRICE3,
                T.PRICE*T.QTY AS SALES,
                T.DISC DISCOUNT    
            FROM TC T
                , BNDL_DFN X
                , SOURCE_DATES D
                , XREF R1
                , PRICE B,
                  WC_PR W
            WHERE D.SOURCE_TABLE = 'CBK'
                AND UPPER (X.LEVEL) = 'CONTRACT' 
                AND X.OFFSET >= 0
                AND D.AS_OF_DATE BETWEEN T.EFFECTIVE_DATE AND T.EXPIRATION_DATE
                AND TRUNC (T.INV_DATE) BETWEEN X.EFF_DATE AND X.EXP_DATE
                AND TRUNC (T.INV_DATE) BETWEEN R1.EFFECTIVE_DATE AND R1.EXPIRATION_DATE
                AND T.CON = X.CONT
                AND T.PRD = X.PRD  
                AND T.PRD = W.PRD
                AND TRUNC (T.INV_DATE) BETWEEN W.EFFECTIVE_START_DATE and W.EFFECTIVE_END_DATE  
                AND UPPER(R1.PURP) = 'OTHER'
                AND (T.CONT = R1.CONT OR T.PR_GROUP = R1.CONT)
                AND T.CONT = B.CONT
                AND T.PRD = B.PRD
                AND TRUNC(T.INV_DATE) BETWEEN B.DT_START AND B.DT_END
        UNION
            SELECT /*+ FULL(T) PARALLEL(T 8)*/ D.SC_ID,
                T.ID,
                T.PRD,
                R1.CONT,
                T.QTY,
                T.PRICE,
                B.PRICE2,
                B.PRICE3,
                T.PRICE*T.QTY AS SALES,
                0 DISCOUNT           
            FROM  TC T
                , BNDL_DFN X
                , SOURCE_DATES D
                , XREF R1
                , PRICE B,
                  WC_PR W
            WHERE D.SOURCE_TABLE = 'CBK'
                AND UPPER (X.LEVEL) = 'CONTRACT' 
                AND X.OFFSET >= 0
                AND D.AS_OF_DATE BETWEEN T.EFFECTIVE_DATE AND T.EXPIRATION_DATE
                AND TRUNC (T.INV_DATE) BETWEEN X.EFF_DATE AND X.EXP_DATE
                AND TRUNC (T.INV_DATE) BETWEEN R1.EFFECTIVE_DATE AND R1.EXPIRATION_DATE
                AND T.PR_GROUP = X.CONT
                AND T.PRD = X.PRD  
                AND T.PRD = W.PRD
                AND TRUNC (T.INV_DATE) BETWEEN W.EFFECTIVE_START_DATE and W.EFFECTIVE_END_DATE  
                AND UPPER(R1.PURP) = 'OTHER'
                AND (T.CONT = R1.XREF OR T.PR_GROUP = R1.XREF)
                AND T.CONT = B.CONT
                AND T.PRD = B.PRD
                AND TRUNC(T.INV_DATE) BETWEEN B.DT_START AND B.DT_END
                AND T.CUST = TO_CHAR (X.TRAD_CUST)
                AND (T.PRICE_GROUP = R1.XREF OR T.CONTRACT = R1.XREF)
        ) a;

        COMMIT;
vg123
  • 1
  • 1
  • How many rows in each of the tables? How many rows does each half of the UNION in the SELECT statement return? – Bob Jarvis - Слава Україні Jan 13 '19 at 03:16
  • Hi Bob,Thank you for your response and formatting the code. Each half of the union query is returning near to 100 rows. Please find here the total number of rows of each table, Table TC is having nearly 20 million , Table X is having 2.5 million rows and the remaining tables are less than 500 rows – vg123 Jan 13 '19 at 03:37
  • Sorry Bob I just re-verified the rows , table TC is having 120 million rows – vg123 Jan 13 '19 at 03:47
  • 1
    Ask your DBA to increase the `TEMP` table space. – Wernfried Domscheit Jan 13 '19 at 14:32
  • Thank you for your response.Our DBA's are not ready to increase the TEMP space instead they ask to refine the query so that it will utilize minimum TEMP space. – vg123 Jan 14 '19 at 02:05

1 Answers1

1

Use a temporary table to execute both halves of your union separately.

INSERT /*+ APPEND */
      INTO  tmp_cont_data
            (   sc_id
              , id
              , prd
              , cont
              , qty
              , price
              , price2
              , price3
              , total_sales
              , total_discount )
    SELECT /*+ FULL(T) PARALLEL(T 8)*/
          d.sc_id
         , t.id
         , t.prd
         , r1.cont
         , t.qty
         , t.price
         , b.price2
         , b.price3
         , t.price * t.qty AS sales
         , t.disc discount
      FROM tc t
         , bndl_dfn x
         , source_dates d
         , xref r1
         , price b
         , wc_pr w
     WHERE d.source_table = 'CBK'
       AND UPPER( x.LEVEL ) = 'CONTRACT'
       AND x.offset >= 0
       AND d.as_of_date BETWEEN t.effective_date AND t.expiration_date
       AND TRUNC( t.inv_date ) BETWEEN x.eff_date AND x.exp_date
       AND TRUNC( t.inv_date ) BETWEEN r1.effective_date AND r1.expiration_date
       AND t.con = x.cont
       AND t.prd = x.prd
       AND t.prd = w.prd
       AND TRUNC( t.inv_date ) BETWEEN w.effective_start_date AND w.effective_end_date
       AND UPPER( r1.purp ) = 'OTHER'
       AND ( t.cont = r1.cont
         OR t.pr_group = r1.cont )
       AND t.cont = b.cont
       AND t.prd = b.prd
       AND TRUNC( t.inv_date ) BETWEEN b.dt_start AND b.dt_end;

INSERT /*+ APPEND */
      INTO  tmp_cont_data
            (   sc_id
              , id
              , prd
              , cont
              , qty
              , price
              , price2
              , price3
              , total_sales
              , total_discount )
    SELECT /*+ FULL(T) PARALLEL(T 8)*/
          d.sc_id
         , t.id
         , t.prd
         , r1.cont
         , t.qty
         , t.price
         , b.price2
         , b.price3
         , t.price * t.qty AS sales
         , 0 discount
      FROM tc t
         , bndl_dfn x
         , source_dates d
         , xref r1
         , price b
         , wc_pr w
     WHERE d.source_table = 'CBK'
       AND UPPER( x.LEVEL ) = 'CONTRACT'
       AND x.offset >= 0
       AND d.as_of_date BETWEEN t.effective_date AND t.expiration_date
       AND TRUNC( t.inv_date ) BETWEEN x.eff_date AND x.exp_date
       AND TRUNC( t.inv_date ) BETWEEN r1.effective_date AND r1.expiration_date
       AND t.pr_group = x.cont
       AND t.prd = x.prd
       AND t.prd = w.prd
       AND TRUNC( t.inv_date ) BETWEEN w.effective_start_date AND w.effective_end_date
       AND UPPER( r1.purp ) = 'OTHER'
       AND ( t.cont = r1.xref
         OR t.pr_group = r1.xref )
       AND t.cont = b.cont
       AND t.prd = b.prd
       AND TRUNC( t.inv_date ) BETWEEN b.dt_start AND b.dt_end
       AND t.cust = TO_CHAR( x.trad_cust )
       AND ( t.price_group = r1.xref
         OR t.contract = r1.xref );

INSERT /*+ APPEND */
      INTO  pfe_gp.cont_data
            (
                sc_id
              , id
              , prd
              , cont
              , qty
              , price
              , price2
              , price3
              , total_sales
              , total_discount
            )
    SELECT a.*
         , SUM( sales ) OVER (PARTITION BY cont) AS total_sales
         , SUM( discount ) OVER (PARTITION BY cont) AS total_discount
      FROM tmp_cont_data a;

It takes 3 statements instead of one, but should improve performace.

Look here for TT details: How do you create a temporary table in an Oracle database?

hendrik_at_geislersoftware
  • 338
  • 1
  • 4