1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora-01652: unable to extend temp space

Discussion in 'SQL PL/SQL' started by zubi, Feb 3, 2019.

  1. zubi

    zubi Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    india
    I have a procedure and a particular query in the procedure is generating about 50GB of temp space causing below exception after few executions.:

    SQL state [72000]; error code [1652]; ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

    DBAs are pointing to the below query in the stored procedure that needs to be rewritten. The tables used in the query below are smaller than 0.1 GB but the query generates 50GB of temp space!

    SELECT tab1.ID ID1, tab2.ID ID2
    FROM (
    SELECT
    OT.ID,
    CONNECT_BY_ROOT OT.UNIQ_ORIG_KEY ORIG_UID
    FROM order_tab OT, status_tab ST
    WHERE OT.otype IN ('A','B')
    AND OT.order_uid IS NULL
    AND OT.BATCH_ID = ST.BATCH_ID
    AND ST.CT_DATE = :A1
    AND ST.BSTATUS = 1
    CONNECT BY PRIOR OT.UNIQ_KEY = OT.UNIQ_ORIG_KEY
    ) tab1 , order_tab tab2
    WHERE tab2.ORD_VERID = 1
    AND tab1.ORIG_UID = tab2.UNIQ_KEY
    ORDER BY ID1;
    Could someone please help in rewriting the query efficiently so that temp space utilization is reduced. Database used Oracle 12c.
     
    Last edited: Feb 3, 2019
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,683
    Likes Received:
    376
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where is the execution plan? What indexes, if any, are on the table? Without that information no one can possibly try to tune that query.

    Provide the requested details.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    777
    Likes Received:
    148
    Trophy Points:
    830
    Location:
    Russian Federation
    The generation temp-space above 50 GB, it is possible with a hierarchical query, as it depends on the amount of data processed.
    And, usually, it "says" about the problems in the request.

    The first step is to optimize the query logically.
    Most likely, a recursive query is "expanded" entirely on a join of tables.
    maybe...

    Code (SQL):
    WITH step1 AS
      (
    SELECT ot.id, ot.uniq_orig_key orig_uid
      FROM order_tab ot, status_tab st
    WHERE ot.otype IN ('A', 'B')
       AND ot.order_uid IS NULL
       AND ot.batch_id = st.batch_id
       AND st.ct_date = :a1
       AND st.bstatus = 1
       AND rownum > 0          
      ),
      step2 AS
      (
        SELECT
          s1.id, connect_by_root s1.uniq_orig_key orig_uid
        FROM step1 s1
        CONNECT BY prior s1.uniq_key = s1.uniq_orig_key
      )        
    SELECT tab1.id id1, tab2.id id2
    FROM
        step2 s2,
        order_tab tab2
    WHERE tab2.ord_verid = 1
       AND s1.orig_uid = s2.uniq_key
    ORDER BY id1;
     
     
    Last edited: Feb 8, 2019