oracle能否不使用undo功能,Oracle Undo 使用

22.6

当我们做DML操作时，Undo就会有一个新的区，其状态是ACTIVE，作为该回话所用，当我们提交该操作后，这个新的区会释放，其所占用空间会放到expired区中，视为可以重用的空间。当我们回滚改操作后，这个新的区也会释放，其所占用空间会放到unexpired区中，直到undo_retention时间后，可以重用改空间。

当我们重启Oracle的时候，如果使用的是immediate方式的话，那么，还未被提交的数据就会被回滚，其所占用的undo空间会释放，也会被放到unexpired区中，作为flashback所用。所以，每次重启数据库后，Undo表空间并不是100%空闲的。

因为Oracle在10g后引入了自动调整undo机制，导致释放unexpired区空间的时间并不一定与undo_retention相等，具体的时间可以查询V$UNDOSTAT表。

相关的Undo监控SQL如下：

---分析UNDO表空间使用情况

SELECT seg.tablespace_name "Tablespace Name", ts.bytes/1024/1024

"TS Size(MB)",

ue.status "UNDO Status", count(*) "Used

Extents",

round(sum(ue.bytes)/1024/1024, 2) "Used

Size(MB)",

round(sum(ue.bytes)/ts.bytes*100, 2) "Used

Rate(%)"

FROM dba_segments seg, DBA_UNDO_EXTENTS ue,

(SELECT tablespace_name, sum(bytes) bytes

FROM

dba_data_files GROUP BY tablespace_name) ts

WHERE ue.segment_NAME=seg.segment_NAME and

seg.tablespace_name=ts.tablespace_name

GROUP BY seg.tablespace_name, ts.bytes, ue.status

ORDER BY seg.tablespace_name;

---分析 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;

--查看回滚段的使用情况，哪个用户正在使用回滚段的资源:

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$syssstat 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=UNDOTBS1 scope=both;

---创建undo表空间

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE

'E:\ORACLE\ORA92\ORCL9\UNDOTBS02.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:\ORACLE\ORA92\ORCL9\UNDOTBS03.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;