oracle中undo解析,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规定的时间内，数据都是有效的，过期后都会