如何在Oracle SQL Developer中执行查询而不耗尽磁盘空间？

报错本质 #

虽然你的查询仅返回2万行结果，但Oracle执行9表左连接时，会在临时表空间存储大量中间计算数据（比如哈希连接的临时哈希表、排序数据）。报错说明当前临时表空间所在的磁盘分区已耗尽，和你看到的40GB空闲磁盘、800GB外部盘无关——大概率临时表空间没挂载到外部盘上。

排查与解决步骤 #

• 第一步：确认临时表空间的存储位置
  执行以下SQL查看临时表空间的文件路径：

  SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, maxbytes/1024/1024 AS max_size_mb FROM dba_temp_files;
  

  用Linux命令检查该路径所在分区的空间使用情况：

  df -h $(dirname <查询到的file_name路径>)
  

  若该分区使用率100%，则验证了临时表空间所在磁盘已满。

• 第二步：临时应急处理

  • 清理占用临时表空间的异常会话：
    先查询关联会话：

    SELECT sid, serial# FROM v$session WHERE username = '你的数据库用户名';
    

    杀死会话：

    ALTER SYSTEM KILL SESSION '<sid>,<serial#>';
    

  • 收缩临时表空间（仅适用于自动扩展的临时表空间）：

    ALTER TABLESPACE temp SHRINK SPACE;
    

• 第三步：永久解决——迁移临时表空间到800GB外部盘

  1. 在外部盘挂载点下创建Oracle可读写的目录（比如/mnt/external_disk/oracle_temp），并设置正确权限：

     mkdir -p /mnt/external_disk/oracle_temp
     chown oracle:oinstall /mnt/external_disk/oracle_temp
     chmod 755 /mnt/external_disk/oracle_temp
     

  2. 创建新的临时表空间：

     CREATE TEMPORARY TABLESPACE temp_new TEMPFILE '/mnt/external_disk/oracle_temp/temp_new01.dbf' SIZE 10G AUTOEXTEND ON NEXT 5G MAXSIZE UNLIMITED;
     

  3. 将数据库默认临时表空间切换为新表空间：

     ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_new;
     

  4. 删除旧的临时表空间（确保无会话使用）：

     DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
     

• 第四步：优化查询减少临时空间占用

  • 检查9个左连接的必要性，移除不必要的表关联
  • 给所有连接字段添加合适的索引，避免全表扫描和大量排序计算
  • 针对小表关联大表的场景，使用嵌套循环连接提示减少临时空间：

    SELECT /*+ USE_NL(a b c) */ ... 你的查询语句 ...
    

内容的提问来源于stack exchange，提问作者innit