发现使用 oracle 临时表空间的进程/查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/174727/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Discover what process/query is using oracle temp tablespace
提问by Simon Gibbs
Oracle FAQ defines temp table space as follows:
Oracle FAQ 定义临时表空间如下：
Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory, space will be allocated in a temporary tablespace for doing the sort operation.
临时表空间用于管理数据库排序操作和存储全局临时表的空间。例如，如果连接两个大表，Oracle 无法在内存中进行排序，则会在临时表空间中分配空间进行排序操作。
That's great, but I need more detail about what exactly is using the space. Due to quirks of the application design most queries do some kind of sorting, so I need to narrow it down to client executable, target table, or SQL statement.
太好了，但我需要更多关于究竟是什么在使用空间的详细信息。由于应用程序设计的怪癖，大多数查询都会进行某种排序，因此我需要将其缩小到客户端可执行文件、目标表或 SQL 语句。
Essentially, I'm looking for clues to tell me more precisely what might be wrong with this (rather large application). Any sort of clue might be useful, so long as it is more precise than "sorting".
本质上，我正在寻找线索来更准确地告诉我这个（相当大的应用程序）可能有什么问题。任何类型的线索都可能有用，只要它比“排序”更精确。
回答by Michael OShea
I'm not sure exactly what information you have to hand already, but using the following query will point out which program/user/sessions etc are currently using your temp space.
我不确定您必须提供哪些信息，但使用以下查询将指出哪些程序/用户/会话等当前正在使用您的临时空间。
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;
Once you find out which session is doing the damage, then have a look at the SQL being executed, and you should be on the right path.
一旦你发现哪个会话正在造成损害，然后看看正在执行的 SQL，你应该在正确的道路上。
回答by Andrew not the Saint
One rule of thumb is that almost any query that takes more than a second probably uses some TEMP space, and these are not the just ones involving ORDER BYs but also:
一个经验法则是，几乎所有需要超过一秒的查询都可能使用一些 TEMP 空间，这些不仅仅是涉及 ORDER BY 的查询，还包括：
- GROUP BYs (SORT GROUPBY before 10.2 and HASH GROUPBY from 10.2 onwards)
- HASH JOINs or MERGE JOINs
- Global Temp Tables (obviously)
- Index rebuilds
- GROUP BY（10.2 之前的 SORT GROUPBY 和 10.2 之后的 HASH GROUPBY）
- HASH JOIN 或 MERGE JOIN
- 全局临时表（显然）
- 索引重建
Occasionally, used space in temp tablespaces doesn't get released by Oracle (bug/quirk) so you need to manually drop a file from the tablespace, drop it from the file system and create another one.
有时，Oracle 不会释放临时表空间中的已用空间（错误/怪癖），因此您需要手动从表空间中删除文件，从文件系统中删除并创建另一个文件。
回答by Najee Ghanim
Thanks goes for Michael OShea for his answer ,
感谢 Michael OShea 的回答，
but in case you have Oracle RAC multiple instances , then you will need this ...
但是如果您有多个 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;
and this the script to generate the kill statements: Please review which sessions you will be killing ...
这是生成终止语句的脚本：请查看您将终止哪些会话......
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;
