有關Undo表空間與回滾段的一些查詢語句記錄

有關Undo表空間與回滾段的一些查詢語句記錄

--與回滾段相關的幾個系統引數

transactions_per_rollback_segment

transactions

max_rollback_segments

rollback_segments 回滾段型別為public則與該引數無關

--相關的幾個檢視:

DBA_UNDO_EXTENTS

GV$UNDOSTAT

V$UNDOSTAT

DBA_ROLLBACK_SEGS

GV$ROLLSTAT

V$ROLLNAME

V$ROLLSTAT

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

[@more@]