发现哪些进程/查询正在使用 Oracle 临时表空间

Question

Oracle FAQ 定义临时表空间如下：

临时表空间用于 管理数据库排序空间 操作和存储全局 临时表。 例如，如果您 连接两个大表，和 Oracle 无法在内存、空间中进行排序 将被临时分配 用于进行排序的表空间 操作。

太好了，但我需要更多有关该空间的具体用途的详细信息。 由于应用程序设计的怪癖，大多数查询都会进行某种排序，因此我需要将其范围缩小到客户端可执行文件、目标表或 SQL 语句。

本质上，我正在寻找线索来更准确地告诉我这个（相当大的应用程序）可能出了什么问题。 任何类型的线索都可能有用，只要它比“排序”更精确即可。

Answer 1

我不确定您必须提供哪些信息，但使用以下查询将指出哪些程序/用户/会话等当前正在使用您的临时空间。

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM v$session a
       , v$sort_usage b
       , v$process c
       , v$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
ORDER BY b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

一旦您发现哪个会话造成了损害，请查看正在执行的 SQL，您应该走在正确的道路上。

Answer 2

感谢 Michael OShea 的回答，

但如果您有 Oracle RAC 多个实例，那么您将需要这个......

SELECT   b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , ROUND (  (  ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb
       , a.inst_ID
       , a.SID
       , a.serial#
       , a.username
       , a.osuser
       , a.program
       , a.status
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP2'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

以及这个脚本来生成终止语句：
请检查您将杀死哪些会话...

SELECT  b.TABLESPACE, a.username , a.osuser , a.program , a.status ,
       'ALTER SYSTEM KILL SESSION '''||a.SID||','||a.SERIAL#||',@'||a.inst_ID||''' IMMEDIATE;'
    FROM gv$session a
       , gv$sort_usage b
       , gv$process c
       , gv$parameter p
   WHERE p.NAME = 'db_block_size'
     AND a.saddr = b.session_addr
     AND a.paddr = c.addr
     -- AND b.TABLESPACE='TEMP'
ORDER BY a.inst_ID , b.TABLESPACE
       , b.segfile#
       , b.segblk#
       , b.blocks;

Answer 3

一个经验法则是，几乎任何花费超过一秒的查询都可能使用一些 TEMP 空间，这些不仅仅是涉及 ORDER BY 的查询，还包括：

1. GROUP BY（10.2 之前的 SORT GROUPBY 和 10.2 以后的 HASH GROUPBY）
2. HASH JOIN或 MERGE JOIN
3. 全局临时表（显然）
4. 索引重建

有时，临时表空间中的已用空间不会被 Oracle 释放（错误/怪癖），因此您需要手动从表空间中删除文件，从文件系统中删除它并创建另一个。