1.查看temp表空间使用率

# 方法一：
select round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) "total MB",round(((f.BYTES_FREE + f.BYTES_USED) - nvl(p.BYTES_USED, 0)) / 1024 / 1024,2) "Free MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) "Used MB",round(nvl(p.BYTES_USED, 0) / 1024 / 1024, 2) /round((f.BYTES_FREE + f.BYTES_USED) / 1024 / 1024, 2) * 100 || '%' "UPRC",d.file_name "fileName" from sys.v_$temp_space_header f,Dba_Temp_Files d,SYS.v_$temp_Extent_Pool p where f.TABLESPACE_NAME(+) = d.tablespace_name and f.FILE_ID(+) = d.file_id and p.FILE_ID(+) = d.file_id and d.tablespace_name = 'TEMP';

# 方法二：

SELECT A.tablespace_name tablespace,
       D.mb_total,
       SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
       D.mb_total - SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free
 FROM v$sort_segment A,
       (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total
          FROM v$tablespace B, v$tempfile C
         WHERE B.ts# = C.ts#
         GROUP BY B.name, C.block_size) D
 WHERE A.tablespace_name = D.name
 GROUP by A.tablespace_name, D.mb_total;

2.确认占用临时表空间高的session及SQLhttp://www.cndba.cn/hbhe0316/article/116508http://www.cndba.cn/hbhe0316/article/116508

Select se.username,se.sid,su.extents,su.blocks*to_number(rtrim(p.value))as    Space,tablespace,segtype,sql_text
from v$sort_usage su,v$parameter p,v$session se,v$sql s
where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr
order by se.username,se.sid;

3.会话使用的排序空间http://www.cndba.cn/hbhe0316/article/116508

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

4.2. 语句使用的临时空间

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

5.和应用确认，查到的SQL使用可以使用alter system kill 杀死这些SID。http://www.cndba.cn/hbhe0316/article/116508

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle