Ora-01652: Unable to Extend Temp Segment by 128 in Tablespace System: How to Extend

ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend?

Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.

To find out if the actual limit is 32gb, run the following:

select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

--------   --------------------   --------------

   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you've currrently set the max file size to (which cannot exceed the aforementioned 32gb):

select bytes/1024/1024 as mb_size,
       maxbytes/1024/1024 as maxsize_set,
       x.*
from   dba_data_files x

MAXSIZE_SET is the maximum size you've set the datafile to. Also relevant is whether you've set the AUTOEXTEND option to ON (its name does what it implies).

If your datafile has a low max size or autoextend is not on you could simply run:

alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;

However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:

alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;

java.sql.SQLException: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP(Even after extending)

The SQL involved is constructing an intermediate result set - most likely because of a join or a sort. There isn't enough space in your TEMP tablespace to hold it. You will either need to add one or more data files to the tablespace (assuming you have enough space on your physical storage to do so), or work with a smaller data set. Alternatively, you could look for ways to modify your SQL to remove joins and sorts where possible. Otherwise, there isn't any workaround.

If you can get an explain plan of the SQL involved it should give you at least a ballpark estimate of how much TEMP space the optimizer is expecting to need.

ORA-01652 Unable to extend temp segment by in tablespace

I found the solution to this. There is a temporary tablespace called TEMP which is used internally by database for operations like distinct, joins,etc. Since my query(which has 4 joins) fetches almost 50 million records the TEMP tablespace does not have that much space to occupy all data. Hence the query fails even though my tablespace has free space.So, after increasing the size of TEMP tablespace the issue was resolved. Hope this helps someone with the same issue. Thanks :)

unable to extend temp segment by 128 in tablespace TEMP

Whilst the standard answer for this would be to get your DBA to extend the TEMP tablespace, I think the problem lies in your query.

Specifically, the way you've written your WHERE clause predicates. I suspect that the first three predicates are meant to be your join predicates, and the last four are supposed to restrict the rows from the course table that are being joined to.

However, what is happening is that the first four predicates are being calculated first (because AND takes precedence over OR) and I suspect that is causing some problems with your joins - possibly some unintended cross joining, and that may be what is unexpectedly blowing up your TEMP tablespace.

To prevent this from happening, you have two possible solutions:

1. Clarify your AND/OR logic with brackets in the correct places:

SELECT DISTINCT
       t4.s_studentreference "Student ID",
       t3.p_surname "Surname",
       t3.p_forenames "Forenames",
       t1.m_reference "Course",
       t2.e_name "Enrolment Name"
FROM   student t4,
       person t3,
       enrolment t2,
       course t1
WHERE  t4.s_id(+) = t3.p_id
AND    t2.e_student = t3.p_id
AND    t2.e_course = t1.m_id
AND    (t1.m_reference LIKE 'LL563%15'
        OR t1.m_reference LIKE 'LL562%15'
        OR t1.m_reference LIKE 'LL563%16'
        OR t1.m_reference LIKE 'LL562%16');

The above groups all the OR statements together and then ANDs them into the rest of the predicates.

2. Use ANSI join syntax and separate out the search predicates from the join predicates:

SELECT DISTINCT
       t4.s_studentreference "Student ID",
       t3.p_surname "Surname",
       t3.p_forenames "Forenames",
       t1.m_reference "Course",
       t2.e_name "Enrolment Name"
FROM   student t4,
       RIGHT OUTER JOIN person t3 ON t4.s_id = t3.p_id
       INNER JOIN enrolment t2 ON t3.p_id = t2.e_student 
       INNER JOIN course t1 ON t2.e_course = t1.m_id
WHERE  t1.m_reference LIKE 'LL563%15'
OR     t1.m_reference LIKE 'LL562%15'
OR     t1.m_reference LIKE 'LL563%16'
OR     t1.m_reference LIKE 'LL562%16';

Of course, the latter doesn't preclude the use of brackets in the right place when you're working with a mix of ANDs and ORs in the where clause...

Option 2 would be my preferred solution - the ANSI join syntax really is the way forward these days when writing SQL.

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP but there is 500GB available

That error can also be a sign of a query needing optimization and sucking up space while running inefficiently. We use the optimizer from Quest (Toad) and check for indexes, analyze the explain plan for problem areas to rework, etc. Look for full table scans that can be eliminated with indexes, Cartesian products, etc.

Pentaho Spoon transformation throws: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

The transformation is performing a sort or creating a temporary data set for a join that requires TEMP space. Depending on your transformation and the explain plan of the query, it might not even matter if you limit the number of rows you are returning if the transformation must process all of the source data before applying your filter. It is very likely that the only way to get this to run is to increase the size of the TEMP tablespace. You will need to work with your DBA to confirm this and work out a solution.

---

Related Topics