ORA-01652: unable to extend temp segment by 128 in tablespace users
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
Solution:
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;
FILE_ID TABLESPACE_NAME AVAILMB UTILIZEMB MAXMB
---------- --------------- -------- --------- --------
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
S
SQLTXPLAIN EXAMPLE .0050048828125 -.000000000931322574615478515625
PERFSTAT SYSAUX .000732421875 -.000000000931322574615478515625
7 rows selected.
ASMCMD> lsdg DATA
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/