ORA-01652: unable to extend temp segment by 128 in tablespace users

“Be alpha”

You find this ora-01652 error often ,when there are huge sorts like group by ,order by and death hash joins which has large sort segments utilizing the PGA and spill over temporary tablespace to save the intermediate result sets happening in the database instance. If PGA is not sufficient to handle the large sorts in memory itself, then intermediate result sets undergo single pass or multipass operation which will undergo sorts in disk and much slower than PGA sorts

Often application administrators have this misconception that adding more space with tempfiles to temp tablespace or having huge temp tablespace will cause the sql queries to run like jet speed. This is not always true because if the sql query doesnot have sorts or group by or hash aggregation works ,then there is no point in adding more tempfiles to temporary tablespace. This will be like adding more oil to fire instead of water. You should know what consume what!

For example: If you want your vehicle to run fast, then instead of filling the fuel tank with water, fill more fuel to it. Adding water will cease your vehicle to run. Likewise, if the sql query (vehicle) with heavy sort want to run fast, then add more PGA and TEMP(fuel) to your database. 😉

An application administrator who ask for more temp space to add without having idea on the sorting operations on the query so that they can get maximum database speed.

check the size of the temporary tablespace

kish@exdbx<>col TABLESPACE_NAME format a10
col TABLESPACE_SIZE format a20
col TABLESPACE_SIZE format 999999
col ALLOCATED_SPACE format 999999
col FREE_SPACE format 999999
select TABLESPACE_NAME,TABLESPACE_SIZE/1048576 MB_TOT,ALLOCATED_SPACE/1048576 MB_USED,FREE_SPACE/1048576 MB_FREE from dba_temp_free_space;kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>kish@exdbx<>

TABLESPACE                                   MB_TOT                                  MB_USED                               MB_FREE
---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
TEMP                                            355                                      355                           332

I use some sql queries with large sorts which use temporary tablespace for performing its sort operations

SQL> select a.country,a.item_type,a.sales_channel,b.order_date,sum(c.total_revenue),b.total_cost,b.total_profit
  2  from test.SALES_TAB_COPY a
  3  inner join test.tabx b on a.ORDER_ID=b.ORDER_ID
  4  inner join test.sales_tab c on b.order_id=c.order_id
  5  group by a.country,a.item_type,a.sales_channel,b.order_date,b.total_cost,b.total_profit
  6  order by 1,2,3,4 desc;

Monitor the sort utilization

kish@exdbx<>select USERNAME,SQL_ID,TABLESPACE,BLOCKS*8192/(1048576) SORTMB,SEGTYPE from v$tempseg_usage;

USERNAME        SQL_ID        TABLESPACE                                        SORTMB SEGTYPE
--------------- ------------- --------------- ---------------------------------------- ---------
TEST            NULL          TEMP                                                  12 HASH


1)SMON background process cleans up the temporary segments and extents automatically

2)Add a new tempfile to the temp tablespace

SQL> alter tablespace temp add tempfile '/home/oracle/oradata/temp02.dbf' size 500M;

3)Set autoextend on for temp tablespace to avoid exhaust errors

kish@exdbx<>alter tablespace temp add tempfile '+DATA' size 5M autoextend on NEXT 5M MAXSIZE 500M;

Tablespace altered.

4)Increase PGA_AGGREGATE_TARGET at session level to avoid one pass and multipass IO’s

5) As a proactive measure to avoid temp errors, a dedicated temp tablespace with big space for the user who always run sort queries on database

A testcase

I was running a query with large sorts due to multiple join conditions and end up with the error ora-01652

SQL> /
select a.country,b.item_type,c.sales_channel,b.total_cost
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

When i kept monitoring the session for temp usage , i can find direct path write temp due to lack sufficient PGA.

USERNAME      SQL_ID     TABLESPACE SPID                     SORT_CNT   SID   PROGRAM         SERIAL# EVENT            USEDMB
---------- ------------- ---------- ------------------------ -------- ------- --------------- ------- --------------- -------
TEST       gqs10z3mw86h4 TEMP       13327                           2      73 sqlplus.exe          23 direct path wri    2767
                                                                                                      te temp

TEST       gqs10z3mw86h4 TEMP       13327                           2      73 sqlplus.exe          23 KSV master wait    2797

kish@exdbx<>select FILE_ID,TABLESPACE_NAME,BYTES/1048576 AvailMB,USER_BYTES/1048576 utilizeMB,MAXBYTES/1048576 MaxMB from dba_temp_files;

---------- --------------- -------- --------- --------
         1 TEMP                2775      2774    32768
         2 TEMP                  15        14      500
         3 TEMP                  15        14      500

kish@exdbx<>select USERNAME,TABLESPACE_NAME,BYTES/1073741824 AvailGB, MAX_BYTES/1073741824 MaxGB from dba_ts_quotas;

USERNAME   TABLESPACE_NAME                                            AVAILGB                                              MAXGB
---------- --------------- -------------------------------------------------- --------------------------------------------------
OLAPSYS    SYSAUX                                             .00860595703125                   -.000000000931322574615478515625
SYSMAN     SYSAUX                                             .08258056640625                   -.000000000931322574615478515625
SQLTXADMIN EXAMPLE                                                          0                   -.000000000931322574615478515625
APPQOSSYS  SYSAUX                                                           0                   -.000000000931322574615478515625
FLOWS_FILE SYSAUX                                                           0                   -.000000000931322574615478515625

SQLTXPLAIN EXAMPLE                                             .0050048828125                   -.000000000931322574615478515625
PERFSTAT   SYSAUX                                               .000732421875                   -.000000000931322574615478515625

7 rows selected.

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  4194304     12000       44                0              44              0             N  DATA/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s