unable to extend temp segment by 128 in tablespace TEMP

Question

I am trying to execute below query in Oracle:

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')

But, I am getting below error:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

I used below query to find temp segment space:

    select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks
 from gv$sort_segment;

Gives:

INST_ID, TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, FREE_BLOCKS
1           TEMP           3199872       15360         3184512

Any idea how to resolve?

Thanks, Aruna

Answer 1

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.