oracle undo 使用分析

当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规定的时间内，数据都是有效的，过期后都会

当产生ORA-30036时，设置_smu_debug_mode=16384 来生产trace文件供分析

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29446986/viewspace-1303535/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29446986/viewspace-1303535/