temp和undo常用的查询命令

关于temp表空间

1.查看消耗临时表空间比较大的sql
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;

2.查看当前sql消耗临时表空间大小
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
  from v$sort_usage sort, v$session sess, v$sql sql
 where sort.SESSION_ADDR = sess.SADDR
   and sql.ADDRESS = sess.SQL_ADDRESS
 order by blocks desc;

关于UNDO表空间

undo已经使用的部分分为不可回收的unexpire和可回收的expire部分

1.查看undo表空间expire和unexpire部分的大小。

select tablespace_name "UNDO表空间名",
sum(bytes)/1024/1024 "占用空间大小(M)",
 status "使用状态"
from  dba_undo_extents 
group by tablespace_name,status;

2.针对居高不下的undo表空间优化

如果undo表空间使用率一直很高，可以考虑将undo设置为自动扩展，并设置maxsize。
这样就会让undo启用最小undo_retention值。

alter database datafile '/oradata/ora/undotbs01.dbf' autoextend on next 5m maxsize 1024m;