TEMP临时表空间 查看占用sql及对象

select d.file_name,d.file_id,d.TABLESPACE_NAME,d.bytes/1024/1024/1024 G_B,d.status,d.AUTOEXTENSIBLE,d.MAXBYTES/1024/1024/1024,d.USER_BYTES/1024/1024/1024 used_G from dba_temp_files d order by used_G desc;


SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks, c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND b.blocks*(select block_size from dba_tablespaces where tablespace_name = b.tablespace) >1024*1024;

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks*8/1024/1024, c.sql_id,c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value;

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, b.blocks*8/1024/1024, c.sql_id,c.sql_text
FROM v$session a, v$sort_usage b, v$sqlarea c
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value order by 6 desc;

31、10235：用于内存堆检查
alter session set events '10235 trace name context forever, level 1';
38、10262：允许连接时存在内存泄漏
alter session set events '10262 trace name context forever, level 300'; -- 允许存在300个字节的内存泄漏
39、10270：转储共享游标
40、10285：模拟控制文件头部损坏
41、10286：模拟控制文件打开错误
42、10287：模拟归档出错
43、10357：调试直接路径机制
44、10500：跟踪SMON进程
alter session set events '10231 trace name context off'; -- 关闭会话期间的数据块检查
event = "10231 trace name context forever, level 10" -- 对任何进程读入SGA的数据块进行检查

释放临时表空间

1
alter tablespace temp shrink space;
　　

--确定TEMP表空间的ts#

1
SELECT TS#, NAME FROM SYS.TS$;
　　

--执行清理操作 temp表空间的TS# 为 3*， So TS#+ 1= 4


BEGIN
FOR CUR IN 1 .. 10
LOOP
EXECUTE IMMEDIATE 'alter session set events ''immediate trace name DROP_SEGMENTS level 4''';
END LOOP;
END;
/
　　

查看占用temp表空间对象；

set lines 200
col username for a20
col b.BYTES_USED/1024/1024/1024||'G' for a30
SELECT a.username,a.sql_id,a.SEGTYPE,b.BYTES_USED/1024/1024/1024||'G',b.BYTES_FREE/1024/1024/1024
FROM V$TEMPSEG_USAGE a
JOIN V$TEMP_SPACE_HEADER b
ON a.TABLESPACE=b.tablespace_name;
 

 

set line 232
set pagesize 49999
set wrap off
col USERNAME for a8
col tablespace for a10
col sql_text for a60
Select se.username,se.sid,se.serial#,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;

　　

TEMP临时表空间的数据文件使用情况

SELECT file_id,TABLESPACE_NAME AS TABLESPACE_NAME,BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED_GB,
BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREE_GB
FROM V$TEMP_SPACE_HEADER
ORDER BY 1 DESC;
　　

删除临时表空间文件

1
ALTER TABLESPACE TEMP DROP TEMPFILE '/u01/app/oracle/oradata/GSP/temp02.dbf';

查看temp表空间的使用情况

col bytes_m for a15
col used_% for a15
col name for a15
col used_m for a15
select d.status ,d.tablespace_name name,
       d.contents ,d.extent_management management,
       to_char(nvl(a.bytes/1024/1024,0),'99,999,990.900') bytes_m,
       nvl(t.bytes,0)/1024/1024||'/'||nvl(a.bytes/1024/1024,0) used_m,
       to_char(nvl(t.bytes/a.bytes*100,0),'990.00') used_% 
    from sys.dba_tablespaces d,
        (select tablespace_name,sum(bytes) bytes from dba_temp_files group by tablespace_name) a , 
        (select tablespace_name,sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name ) t 
    where d.tablespace_name=a.tablespace_name(+) 
     and d.tablespace_name=t.tablespace_name(+) 
     and d.contents like 'TEMPORARY';

临时表空间 extent management 为local 正常
如果临时表空间使用率高，不可控制smon进程做处理，此时可以如下命令来加快处理
1.alter session set events 'immediate trace name drop_segments level tablespace_number+1';

2.切换temp tablespace
3.建立temp group 

分析使用临时表空间的对象

set lines 300 pages 2000
col username for a10
col machine for a10
col program for a14 trunc 
col event for a20 trunc 
col sqltext for a30
col sql_id for a15 
col wai_secinwait for a10 
col bs for a10 
col ch# for 999
select ses.username,ses.sid,case when ses.state != 'WAITING' then 'On CPU / runqueue' 
                                  else 
								      event 
								   end as event,
		ses.machine,ses.program,ses.status,ses.last_call_et,sql.hash_value,ses.sql_id,
		wait_time||':' || seconds_in_wait wai_secinwait,
		seq#,
		row_wait_obj#,
		substr(sql.sql_text,1,30) sqltext,
		blocking_instance||':'|| blocking_session bs,
		sql_child_number ch# 
	from v$session ses 
	left join v$sql sql 
	on ses.sql_hash_value=sql.hash_value 
	and ses.sql_child_number=sql.child_number 
	where ses.type='USER' 
	and ses.status<> 'INACTIVE' 
	-- and sql_text like 'select t.subsid,s.sernubmer,t%' 
	order by seconds_in_wait,last_call_et,4;

查看临时表空间占用情况

select u.tablespace,u.contents,u.segtype,u.extents,u.blocks,round(((u.blocks*p.value)/1024/1024),2) mb,
s.sql_id from v$session s ,v$sort_usage u,sys.v_$system_parameter p 
where s.saddr=u.session_addr 
and upper(p.name)='DB_BLOCK_SIZE' order by mb;

no rows selected 表示空闲无使用;
配合v$temporary_lobs 

查看真正消耗temp高的sql

select k.inst_id inst_id,
      ktssoses "saddr",
      sid,
      ktssosno "serial#',
      username "username",
      osuser,ktssosqlid "SQL_ID",
      ktssotsn "TABLESPACE",
      decode(ktssocnt,0,'PERMANENT',1,'TEMPORARY') "CONTENTS",
      --注意在12c的v$sort_usage定义中tablespace 和contents已经发生变化了。  decode(ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX','UNDEFINED') "SEGTYPE",
      ktssofno "SEGFILE#",
      ktssobno "SEGBLK#",
      ktssoexts "EXTNETS",
      ktssoblks "BLOCKS",
      round(ktssoblks*p.value/1024/1024,2) "SIZE_MB",
      ktssorfno "SEGRFNO#" 
    from x$ktsso k ,
    v$session s,
    (select value from v$parameter where name='db_block_size') P 
    where ktssoses = s.saddr 
    and ktssosno=s.serial#;

分析某一时间段内的temp使用情况

select * 
  from (select t.sample_time,s.parsing_schema_name,
        t.sql_id,t.sql_child_number as sql_child,
        round(t.temp_space_allocated/1024/1024/1024,2) ||' G' as temp_used,
        round(t.temp_space_allocated/(select sum(decode(d.autoextensible,'YES',d.maxbytes,d.bytes)) from dba_temp_files d),2) * 100||'%' as temp_pct,
        t.program,
        t.module,
        s.sql_text 
    from v$active_session_history t,v$sql s 
    where t.sample_time> to_date('2024-07-04 10:00:00','yyyy-mm-dd hh24:mi:ss') and 
         t.sample_time < to_date('2024-07-04 11:00:00','yyyy-mm-dd hh24:mi:ss') and t.temp_space_allocated is not null 
         and t.sql_id=s.sql_id 
         order by t.temp_space_allocated desc) 
         where rownum <50 
         order by temp_used desc;