认真就输

今天一个朋友问：他们生产环境TEMP表空间使用100%，已经KILL掉所有的会话，仍然是100%，立马就想到特别把TEMP表空间当普通表空间来统计了。下面是我常用于统计TEMP表空间的脚本

欢迎大家加入ORACLE超级群：17115662 免费解决各种ORACLE问题,以后BLOG将迁移到http://www.htz.pw

set pages 9999;
set echo off
set lines 800;
set feedback off
col sess for a10;
col status for a10;
col username for a20;
col client for a25;
col osuser for a10;
col program for a30;
col max_gb for 999999.99
col temp_gb for 999999.99
col used_gb for 999999.99
col allocated_gb for 999999.99
col free_gb for 999999.99
col alloc_free% for 999.99
col max_free% for 999.99
col tablespace_name for a16
prompt ****************** temp tablespace ****************
select * from (select c.tablespace_name,
sum(decode(c.maxbytes, 0, c.bytes, maxbytes)) / 1024 / 1024 / 1024 max_gb,
sum(c.bytes) / 1024 / 1024 / 1024 temp_gb
from dba_temp_files c
group by tablespace_name) b;
prompt ******************* user temp tablespace *************************
select tablespace,
sum(a.blocks * b.value / 1024 / 1024 / 1024) used_temp_gb
from v$sort_usage a, v$parameter b
where b.name = ‘db_block_size’
group by tablespace;
prompt ***************** user % temp tablespace ***************************
select d.tablespace_name,
d.max_gb,
d.temp_gb allocated_gb,
d.temp_gb – e.used_gb free_gb,
(d.temp_gb – e.used_gb)*100/d.temp_gb “alloc_free%”,
(d.max_gb – e.used_gb)*100/d.max_gb “max_free%”
from (select c.tablespace_name,
sum(decode(c.maxbytes, 0, c.bytes, maxbytes))/1024/1024/1024 max_gb,
sum(c.bytes)/1024/1024/1024 temp_gb
from dba_temp_files c
group by tablespace_name) d,
(select sum(nvl(a.blocks, 0) * b.value/1024/1024/1024) used_gb
from v$sort_usage a, v$parameter b
where b.name = ‘db_block_size’
group by a.tablespace) e;
prompt ****************** about session with user temp tablespace ****************
select /*+ rule */ s.sid || ‘,’ || s.serial# as sess,
s.username,
s.status,
substr(s.program, 1, 39) program,
s.osuser || ‘@’ || s.machine || ‘@’ || s.process as client,
u.blocks * b.value / 1024 / 1024 sort_mb,
a.hash_value sess_hash_value,
s.osuser,
to_char(s.logon_time, ‘mm-dd hh24:mi’) as logon_time
from v$session s, v$sort_usage u, v$sqlarea a, v$parameter b
where s.saddr = u.session_addr
and s.sql_address = a.address
and b.name = ‘db_block_size’;