Have you ever faced this error ora-01652 ?

The above error is also similar to

ora-01652 : Unable to extend temp segment by 256 in tablespace

Every Oracle professional might have faced this error serveral times. In thi post i will discuss about the error , Why the error “Ora-01652 ” occurs and how to solve this problem .

Most propably if you faced the this error while inserting , updating the data , then this might be a space issue.
Oracle allocates the space required for a table expansion in the form of extents. if there is insufficient space in the data files that contribute your tablespace,then oracle throws this error.

If you faced this problem while inserting or updating the data , try to look at the free space available in each tablespace.

 If the tot_pct_used is nearing 98%, then you need to add some space to your tablespace in the form of data files.

You can achieve this by the following statement

ALTER TABLESPACE users
ADD DATAFILE 'c:\oraexp\oradata\users23.dbf' SIZE 100M;

If you face the Ora-01652 error while running a query then there is a different twist to the story.If your query uses heavy sorting, huge joins then this error can also occur.

Oracle utilizes the temp tablespace for sorting and other operations. Ora-01652 error occur if the sorting operation is so huge that the oracle cannot accomodate no more space from the temp tablespace.

A sort operation cannot necessarily be an “Order by” clause. Clauses like “Group by“, “Union“, “Sort Merge Join” Involves sorting.

 Before adding the space to the temp tablespace straight-a-way you need to monitor the temp tablespace usage. And efforts are required to change the query that is responsible for the above error.
You cannot add space blindly to the temp tablespace.

First you need to check the sort usage and temp space usage.

You can check the temp space usage with the following dictionary views.

v$tablespace
v$tempfiles
v$temp_space_header

v$tablespace - Provides the list of all tablespaces in your instance.
v$tempfiles -  Provides the list of the files , status, size allocated for the temporary tablespace.
v$temp_space_header - Provides the dynamic view of temp files free space and used space.

You can continously monitor the temp tablespace usage with the below query.

select a.name temp_tablespace_name, a.TS# temp_TABLESPACE_NUMBER,FILE# temp_file_NO,CREATION_TIME ,STATUS temp_file_status,bytes/1024 temp_file_size_KB,b.name temp_file_name, C.BYTES_USED/1024 TEMP_FILE_BYTES_USED_KB ,C.BYTES_FREE/1024 TEMP_FILE_BYTES_FREE_KB from v$tablespace a, v$tempfile b, V$TEMP_SPACE_HEADER C where a.ts#=b.ts# AND B.RFILE#=C.RELATIVE_FNO

If Your temp tablespace is getting piled up with in seconds then there should be a problem with the configuration or query.

You can the sort space usage with the v$sort_usage dictionary view.

 SELECT /*+ ORDERED */ u.tablespace, s.sid || ',' || s.serial# as sid_ser, s.username, s.osuser, (SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb, x.sql_text, s.module || ':' || chr(10) ||'. ' || s.action as process FROM v$sort_usage u ,v$session S ,v$sql X ,v$parameter a WHERE s.saddr = u.session_addr AND s.sql_address = x.address AND s.sql_hash_value = x.hash_value AND a.name = 'db_block_size' GROUP BY u.tablespace , s.sid , s.serial# , s.username , s.osuser , a.value , x.sql_text , s.module , s.action ORDER BY u.tablespace , s.sid

If you see that your sort_usage is more try to add more space to the temp tablespace.

You can increase the temp tablespace size by adding a file to it with the following command.

alter tablespace temp add tempfile 'C:\ORACLEXE\ORADATA\XE\TEMP1.DBF' SIZE 500M

Even after increasing the temp tablespace size to three fold or four fold , if the error Ora-01652 occurs

1.create a new temp tablespace with a different name
2.Make it as default temp tablespace.
3.Bring the old temp files offline.
4.drop the Old temp tablespace.

create temporary tablespace TEMP3 tempfile 'c:\oraclexe\oradata\xe\temp5.dbf' size 500m; alter database default temporary tablespace TEMP3;alter database tempfile 'C:\ORACLEXE\ORADATA\XE\TEMP.DBF' OFFLINE;drop tablespace TEMP;

After this, if you face the above error bounce the Oracle database. Even after that if you face Ora-01652 error check your configuration settings in INIT.ORA file like v$sort_area_size and try to contact Oracle meta-link.