如何處理Oracle的UNDO表空間所對應的數據文件過大問題

故障現象：在AIX查看df -g空間，查看到對應的數據庫undo表空間達到90%多，下面處理表空間的數據文件過大問題

--1 查看undo的表空間大小和最大值
select   t.file_name,t.tablespace_name,
t.bytes/1024/1024/1024 "GB",  t.maxbytes/1024/1024/1024   "Max GB"
-
 

 from     dba_data_files     t     where    t.tablespace_name='UNDOTBS1'
 
 --數據文件為：/Oracle/oradata/undo/undotbs01.dbf
 
 --2 創建一個新的undo表空間，用來替換原來的undo表空間
create      undo     tablespace    UNDOTBS2
datafile     '/Oracle/oradata/log/undotbs02.dbf'
size    10M    autoextend     on    maxsize    unlimited;

--3 把新的undo表空間設置成數據庫的undo表空間
alter     system     set undo_tablespace=UNDOTBS2     scope=both;

--4 再次驗證數據庫的undo表空間
 show     parameter     undo_tablespace

--5 等待原UNDO表空間UNDOTBS1 is OFFLINE；

 
 SELECT    r.status    "Status",
r.segment_name    "Name",
r.tablespace_name     "Tablespace",
s.extents     "Extents",
TO_CHAR((s.bytes/1024/1024),'99999990.000')     "Size"
FROM     sys.dba_rollback_segs      r, sys.dba_segments    s
WHERE        r.segment_name = s.segment_name
AND       s.segment_type IN ('ROLLBACK', 'TYPE2 UNDO')
and       r.tablespace_name='UNDOTBS1'      and       status='ONLINE'

如果上面有狀態online的對象，可以查詢具體對象的sid，serial#

--5.1 查看當前是什么在使用這個回滾段
 SELECT     r.NAME,s.sid,s.serial# Serial,
s.username ,s.machine ,
t.start_time,t.status ,
t.used_ublk ,
substr(s.program, 1, 15)    "operate"
FROM      v$session    s, v$transaction    t, v$rollname    r,v$rollstat    g
WHERE      t.addr = s.taddr
AND      t.xidusn = r.usn
AND     r.usn = g.usn
ORDER     BY     t.used_ublk desc;

--比如：對象為：sid  474,serial  6794

--5.2 根據sid查出具體的sql
select     sql_text    from     v$session a,v$sqltext_with_newlines     b
  where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
  and      a.sid=&sid    order     by     piece

如果該sql不重要，可以直接kill該會話。
 
--5.3  kill session
alter system kill session '474,6794';

 --5.4  刪除原undo表空間及其系統的數據問題
drop tablespace UNDOTBS1 including contents and datafiles;
（在AIX系統中，雖然已經刪除了系統所對應的undo表空間的數據文件，但用df -g查看，該系統空間不能釋放。
主要是由于Oracle的一個進程在訪問該文件。可以kill Oracle訪問進程，或者重啟數據庫后，即可釋放系統的空間。）
 
 
 --6新建立UNDOTBS1表空間
create     undo     tablespace    UNDOTBS1
datafile    '/Oracle/oradata/undo/undotbs01.dbf'
size    10M    autoextend   on    maxsize 12G;
 

--7切換回UNTOTBS1
alter system set undo_tablespace=UNDOTBS1 scope=both;

--8 等待UNDO表空間UNDOTBS2 is OFFLINE；
 SELECT r.status "Status",
r.segment_name "Name",
r.tablespace_name "Tablespace",
s.extents "Extents",
TO_CHAR((s.bytes/1024/1024),'99999990.000') "Size"
FROM sys.dba_rollback_segs r, sys.dba_segments s
WHERE r.segment_name = s.segment_name
AND s.segment_type IN ('ROLLBACK', 'TYPE2 UNDO')
and r.tablespace_name='UNDOTBS2'
ORDER BY 5 DESC;

--9 刪除
drop    tablespace     UNDOTBS2    including    contents    and     datafiles;

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]