临时表空间和undo表空间

最新推荐文章于 2023-11-21 22:34:19 发布

原创最新推荐文章于 2023-11-21 22:34:19 发布 · 480 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了如何检查Oracle数据库中临时表空间和Undo表空间的使用情况。提供了查询临时表空间详细使用状态的方法，并给出了获取Undo表空间使用概况的SQL语句。

查看临时表空间的具体使用情况
简易版

select s.username, s.sid, u.SQL_ID, u.tablespace, u.contents, u.segtype,     
round(u.blocks*8192/1024/1024,2) MB                                
from v$session s, v$sort_usage u                                   
where s.saddr = u.session_addr                                     
and u.contents = 'TEMPORARY'                                       
order by MB DESC ;

详细

select k.inst_id "INST_ID",  
       ktssoses "SADDR",  
       sid,  
       ktssosno "SERIAL#",  
       username "USERNAME",  
       osuser "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 "EXTENTS",  
       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#;

–undo使用概况

select tablespace_name,status,sum(bytes)/1024/1024 as space_MB
from dba_undo_extents
group by tablespace_name,status
order by 1;

select file_name,bytes/1024/1024/1024 g from dba_data_files where tablespace_name=‘UNDOTBS1’

查询使用undo的sql

select s.sid,s.serial#,s.sql_id,v.usn,r.segment_name,r.status,v.rssize/1024/1024 mb,c.bytes/1024/1024/1024 g from 
dba_rollback_segs r,v$rollstat v,v$transaction t,v$session s,dba_segments c
where r.segment_id=v.usn and 
v.usn=t.xidusn and t.addr=s.taddr 
and r.segment_name=c.segment_name;


select a.sid,a.event,a.CLIENT_INFO,a.MACHINE,a.username,a.status,a.sql_id,a.sql_child_number,b.sql_text
  from v$session a,v$sql b
 where a.sql_address=b.address
   and a.sql_hash_value=b.hash_value
   and a.sql_child_number=b.child_number
 order by 1 desc;

--事务回滚时间
select undoblockstotal "Total",
       undoblocksdone "Done",
       undoblockstotal - undoblocksdone  "ToDo",
       decode(cputime,
              0,
              'unknown',
              to_char(sysdate + (((undoblockstotal - undoblocksdone)
                        / (undoblocksdone / cputime)) / 86400),
                      'yyyy-mm-dd hh24:mi:ss'))  "Estimated time to complete"，to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') 
  from v$fast_start_transactions;