如何在Oracle中监视临时表空间使用情况？

问题：

您要监视Oracle中临时表空间的使用。

解：

我们可以使用以下查询找出临时表空间中已使用和可用的空间。

我们将从识别临时表空间名称开始。

示例

SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

输出结果

TEMP

接下来，我们将使用下面的SQL来标识临时表空间中的已用空间和可用空间。

示例

SELECT *
FROM
  (SELECT a.tablespace_name,
    SUM(a.bytes/1024/1024) allocated_mb
  FROM dba_temp_files a
  WHERE a.tablespace_name = 'TEMP'
  GROUP BY a.tablespace_name
  ) x,
  (SELECT SUM(b.bytes_used/1024/1024) used_mb,
    SUM(b.bytes_free      /1024/1024) free_mb
  FROM v$temp_space_header b
  WHERE b.tablespace_name = 'TEMP'
  GROUP BY b.tablespace_name
  );

输出结果

TEMP    4600    4568    32

我们将识别造成临时表空间高使用的用户和SQL语句。

示例

SELECT s.sid
  || ','
  || s.serial# sid_serial,
  s.username,
  o.blocks * t.block_size / 1024 / 1024 mb_used,
  o.tablespace,
  o.sqladdr address,
  h.hash_value,
  h.sql_text
FROM v$sort_usage o,
  v$session s,
  v$sqlarea h,
  dba_tablespaces t
WHERE o.session_addr = s.saddr
AND o.sqladdr        = h.address (+)
AND o.tablespace     = t.tablespace_name
ORDER BY s.sid;

我们可以使用以下查询来找出哪些会话正在使用临时表空间中的空间。

示例

SELECT s.sid
  || ','
  || s.serial# sid_serial,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  SUM (o.blocks) * t.block_size / 1024 / 1024 mb_used,
  o.tablespace,
  COUNT(*) sorts
FROM v$sort_usage o,
  v$session s,
  dba_tablespaces t,
  v$process p
WHERE o.session_addr = s.saddr
AND s.paddr          = p.addr
AND o.tablespace     = t.tablespace_name
GROUP BY s.sid,
  s.serial#,
  s.username,
  s.osuser,
  p.spid,
  s.module,
  s.program,
  t.block_size,
  o.tablespace
ORDER BY sid_serial;