關於oracle中的undo

黄多鱼發表於2024-07-05
Oracle

一，undo介紹

二，undo檢視說明

三，常用指令碼說明

四.釋放UNDO表空間

五.參考

一，undo介紹
Oracle中 undo的作用主要有兩個：第一是回滾事務，第二是產生一致性讀。同時也衍生出了一些新的 功能，比如Flashback query。傳統的 undo是透過undo segment 來管理的，我們看下面的示例：

詳見第二篇文章《oracle undo原理》

二，undo檢視說明
DBA_ROLLBACK_SEGS
V$ROLLSTAT
V$TRANSACTION
V$UNDOSTAT
DBA_UNDO_EXTENTS
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database. This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS.STATUS有三個值：

ACTIVE 表示未提交事務還在使用的UNDO EXTENT，該值對應的UNDO SEGMENT的DBA_ROLL_SEGMENTS.STATUS一定是ONLINE或PENDING OFFLINE狀態，一旦沒有活動的事務在使用UNDO SEGMENT，那麼對應的UNDO SEGMENT就變成OFFLINE狀態。
EXPIRED 表示已經提交且超過了UNDO_RETENTION指定時間的UNDO EXTENT。
UNEXPIRED 表示已經提交但是還沒有超過UNDO_RETENTION指定時間的UNDO EXTENT。
Oracle重複使用UNDO EXTENT的原則如下：
ACTIVE狀態的EXTENT在任何情況下都不會被佔用。
如果是自動擴充套件的UNDO表空間，Oracle會保證EXTENT至少保留UNDO_RETENTION指定的時間。
如果自動擴充套件空間不足或者UNDO表空間是非自動擴充套件，Oracle會嘗試重複使用同一個段下面EXPIRED狀態的EXTENT，如果本段中沒有這樣的EXTENT，就會去偷別的段下面EXPIRED狀態的EXTENT，如果依然沒有這樣的EXTENT，就會使用本段UNEXPIRED的EXTENT，如果還是沒有，那麼會去偷別的段的UNEXPIRED的EXTENT，這個都沒有，就會報錯。
三，常用指令碼說明
1.undo表空間總大小

select tablespace_name,contents from dba_tablespaces where tablespace_name='UNDOTBS1';

select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files where tablespace_name='UNDOTBS1'  group by tablespace_name;

2.檢視undo表空間的使用情況 有兩個檢視可查

select segment_name, v.rssize/1024/1024 mb
    From dba_rollback_segs r, v$rollstat v
    Where r.segment_id = v.usn(+)
    order by segment_name ;

select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1'

3.查詢事務使用的UNDO段及大小

-- 我的UNDO表空間超過了90%，是哪些會話的事務佔用了這些空間：

select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb
    From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s
    Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
    order by segment_name ;

進一步查詢，可知下面的sql佔用了大部分的undo

4.查詢每秒使用的undo表空間大小

select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24))  1024  1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (sum(undoblks)  sum(((end_time - begin_time) * 86400))) ups
          from v$undostat),
       (select value as dbs from v$parameter where name = 'db_block_size');

5.根據Oracle對UNDO表空間的統計資訊調整UNDO引數及大小

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,
  TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,
  UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON",
  MAXQUERYLEN, TUNED_UNDORETENTION
  FROM v$UNDOSTAT;

通常當欄位UNXPSTEALCNT和EXPBLKREUCNT是非零值，表示有空間壓力。如果欄位SSOLDERRCNT是非零值，表示UNDO_RETENTION設定不合理。如果欄位NOSPACEERRCNT是非零值，表示有一系列空間問題。在10g DBA_HIST_UNDOSTAT檢視包括了V

KTUSMST2將沒有資料生成，該表是DBA_HIST_UNDOSTATS檢視的源表。

V$UNDOSTAT檢視，該檢視的作用是用於指導管理員調整UNDO表空間的引數及表空間大小，每行表示的是10分鐘的資料，最多可保留576行，4天一個週期，如果該檢視沒有資料，那麼UNDO可能是手動管理方式。下面對該檢視欄位的含義進行說明：

6.查詢undo表空間extent的使用情況

select sum(bytes)/1024/1024 MB, status, tablespace_name
  from dba_undo_extents
  group by status, tablespace_name order by 3, 2;

四.釋放UNDO表空間
詳見：http://blog.itpub.net/23135684/viewspace-1065601/

新建一個undo表空間，然後修改undo_tablespace指向新的表空間，然後查詢下面的檢視，待原來的表空間上所有的段都為offline狀態時才能刪除。

查詢

set line 200 pages 999
col segment_name format a30
col tablespace_name format a30
select segment_name, tablespace_name, r.status,
    (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
    max_extents, v.curext CurExtent
    From dba_rollback_segs r, v$rollstat v
    Where r.segment_id = v.usn(+)
    order by segment_name ;

五.參考
監控和管理Oracle UNDO表空間的使用[1]

UNDO表空間監控說明[2]

裡面有MOS中對AUM和SUM的描述

相關文章