oracle undo 使用分析

select sum(bytes) from dba_free_space where tablespace_name='';
select sum(bytes) from dba_data_files where tablespace_name='';

select sum(blocks) "UNEXPIRED BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='UNEXPIRED';
select sum(blocks) "EXPIRED BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='EXPIRED';

select sum(blocks) "ACTIVE BLOCKS" from dba_undo_extents where tablespace_name='UNDOTBS1'and status='ACTIVE';

select count(*) from dba_rollback_segs where status='OFFLINE';

Show parameter Undo

select max(tuned_undoretention) from v$undostat;

---分析 UNDO 的使用情況 
 
SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS "Size M",
COUNT(*) Undo_Extent_Num
FROM DBA_UNDO_EXTENTS
GROUP BY TABLESPACE_NAME, STATUS;
 
---監控undo表空間
 
SELECT BEGIN_TIME, END_TIME, UNDOTSN, UNDOBLKS, TXNCOUNT,
MAXCONCURRENCY AS "MAXCON"
FROM V$UNDOSTAT;
 
--查詢是否有回滾段的爭用
 
select * from v$waitstat;
 
SELECT name, waits, gets, waits/gets "Ratio" 
FROM v$rollstat a, v$rollname b 
WHERE a.usn = b.usn;
 
---檢視回滾段的統計資訊：
 
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
FROM v$rollname n, v$rollstat s
WHERE n.usn = s.usn;



-----檢視哪個會話，佔用回滾段過多 ，根據SID查詢對應的語句
column tx_addr format a18
column program format a15
column status format a10
column machine format a10
column tbs_name format a10
column start_time format a18
column undo_size_mb format 9999990.00


select s.sid,
       substr(s.program, 1, 15) program,
       substr(s.machine, 1, 10) machine,
       t.xidusn || '.' || t.xidslot || '.' || t.xidsqn tx_addr,
       t.status,
       t.start_time,
       tbs.tablespace_name tbs_name,
       round(t.used_ublk * tbs.block_size / 1048576, 2) undo_size_mb,
       t.used_urec
  from v$transaction t, v$session s, v$parameter p, dba_tablespaces tbs
 where t.ses_addr = s.saddr
   and p.name = 'undo_tablespace'
   and p.value = tbs.tablespace_name
 order by round(t.used_ublk * tbs.block_size / 1048576, 2) desc;

-----檢視回滾段的使用情況，哪個使用者正在佔用的回滾段最多:
 
SELECT s.sid,s.username,s.PROGRAM,s.MACHINE,u.name,t.used_ublk
FROM v$transaction t, v$rollstat r, v$rollname u, v$session s
WHERE s.taddr = t.addr
AND t.xidusn = r.usn
AND r.usn = u.usn
ORDER BY s.username;


-查詢回滾段的事務回退率
 
transaction rollbacks/(transaction rollbacks+user commits)
 
select name,value from v$sysstat where name in ('user commits','transaction rollbacks');


 
--查詢獲取回滾段資料的時候資料緩衝區中copy的資料塊的數量
 
select count(*) from x$bh where state=3;
 
--查詢在SGA中回滾段的塊的數量USN=n,則回滾段頭class為11+2n,回滾段塊為12+2n
 
select usn from v$rollstat;
 
select class,count(*) from x$bh where class>10 group by class;
 
--查詢資料庫的的回滾段情況
 
select segment_id,segment_name from dba_rollback_segs;
 
--指定使用某個回滾段
 
set transaction use rollback segment _SYSSMU4$
 
--查詢回滾段在使用,擴充套件,回縮的時候extent在迴圈的次數
 
select usn,wraps from v$rollstat;
 
--查詢回滾段收縮的情況
 
select usn,optsize,shrinks from v$rollstat;
 
--切換undo表空間到新的表空間(注意修改pfile或者spfile引數)
 
alter system set undo_tablespace=UNDO3 scope=both sid='oyy1a';
 
---建立undo表空間 
 
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'E:ORACLEORA92ORCL9UNDOTBS02.DBF' size 11M reuse AUTOEXTEND ON;
 
---改變(Altering) an Undo Tablespace
 
Adding a datafile 
 
Renaming a datafile 
 
Bringing a datafile online or taking it offline 
 
Beginning or ending an open backup on a datafile 
 
---增加資料檔案
 
ALTER TABLESPACE UNDOTBS2 
 
ADD DATAFILE 'E:ORACLEORA92ORCL9UNDOTBS03.DBF' size 2M AUTOEXTEND ON NEXT 1M 
 
MAXSIZE UNLIMITED;
 
--drop undo表空間
 
DROP TABLESPACE UNDOTBS2; ---INCLUDING CONTENTS.
 
--不指定undo表空間
 
ALTER SYSTEM SET UNDO_TABLESPACE = '';
 
--設定retention值:
 
ALTER SYSTEM SET UNDO_RETENTION = 5;
 
 
select tablespace_name,status,sum(bytes)/1024/1024 "bytes(M)"
from dba_undo_extents
group by tablespace_name,status 
order by tablespace_name;
 
select tablespace_name,status,blocks,sum(bytes)/1024/1024 "bytes(M)"
from dba_undo_extents
group by tablespace_name,status,blocks
order by tablespace_name;
 
檢視undo表空間使用的表空間下還有多少可以回退的空間，EXPIRED是可以會退的，ACTIVE是正在用的，UNEXPIRED是系統保留的和undo_retention=600有關
UNEXPIRED 和EXPIRED　是已使用的undo 表空間，其中expired 說明是已經過期的資料，也就是15分鐘（預設情況）以外的資料，以被覆蓋，可以認為是空閒的。
 
 
UNDO 表空間是會被重用的,只有當事務沒結束,或開了retention g rantee,或在undo_retention時間內不能被重用。
 
在undo_retention規定的時間內，資料都是有效的，過期後都會