ORA-1652 Unable To Extend Temp Space Generated From SYS_AUTO_SQL_TUNING_TASK (Doc ID 2915198.1)

Last updated on JULY 20, 2024

Applies to:

Goal

When SYS_AUTO_SQL_TUNING_TASK runs it can generate
ORA-1652 unable to extend temp segment by 32 in tablespace TEMP

When looking at the resulting 1652 errorstack trace sql_id 49xvssdzd07su is referenced

     WITH snap_ranges AS
(
         SELECT   /+ FULL(st) / sn.dbid ,
                  sn.instance_number ,
                  sn.startup_time ,
                  st.stat_id ,
                  st.stat_name ,
                  Min(sn.snap_id)                        AS min_snap ,
                  Max(sn.snap_id)                        AS max_snap ,
                  Min(Cast(begin_interval_time AS DATE)) AS min_date ,
                  Max(Cast(end_interval_time AS   DATE)) AS max_date
         FROM     awr_root_snapshot SN ,
                  awr_root_stat_name ST
         WHERE    sn.begin_interval_time > Trunc(sysdate) - 7
         AND      sn.dbid = st.dbid
         AND      st.stat_name IN ('DB time')
         GROUP BY sn.dbid,
                  sn.instance_number,
                  sn.startup_time,
                  st.stat_id,
                  st.stat_name ),delta_data AS
(
       SELECT sr.dbid ,
              sr.instance_number ,
              sr.stat_name ,
              CASE
                     WHEN sr.startup_time BETWEEN sr.min_date AND    sr.max_date THEN tm1.value + (tm2.value tm1.value)
                     ELSE (tm2.value tm1.value)
              END AS delta_time
       FROM   awr_root_sys_time_model tm1 ,
              awr_root_sys_time_model tm2 ,
              snap_ranges sr
       WHERE  tm1.dbid = sr.dbid
       AND    tm1.instance_number = sr.instance_number
       AND    tm1.snap_id = sr.min_snap
       AND    tm1.stat_id = sr.stat_id
       AND    tm2.dbid = sr.dbid
       AND    tm2.instance_number = sr.instance_number
       AND    tm2.snap_id = sr.max_snap
       AND    tm2.stat_id = sr.stat_id )
SELECT /+ gather_plan_statistics / round(sum(delta_time/1000000),2) AS dbtime_7day_secs

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.