无法在表空间temp中将TEMP段扩展128

Question

我正尝试在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')

但是，我得到了下面的错误：

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.

我使用下面的查询来查找临时段空间：

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

提供：

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

你知道怎么解决吗？

谢谢，阿鲁娜

Answer 1

虽然解决这个问题的标准答案是让DBA扩展临时表空间，但我认为问题出在您的查询中。

具体地说，就是您编写WHERE子句谓词的方式。我怀疑前三个谓词是您的联接谓词，后四个谓词是为了限制正在联接的course表中的行。

然而，发生的情况是，前四个谓词是首先计算的(因为AND优先于OR)，我怀疑这会导致您的连接出现一些问题-可能是一些意外的交叉连接，这可能是意外地炸毁您的临时表空间的原因。

为了防止这种情况发生，您有两种可能的解决方案：

1.在正确的位置使用括号来阐明您的AND/OR逻辑：

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

上面的语句将所有OR语句组合在一起，然后将它们与其余谓词进行and运算。

2.使用ANSI连接语法并将搜索谓词从连接谓词中分离出来：

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

当然，当您在where子句中混合使用and和Of时，后者并不排除在正确的位置使用括号...

选项2将是我首选的解决方案-- ANSI join语法是目前编写SQL时的发展方向。