對于undotbs01.dbf文件太大的處理辦法

縮小Oracle目錄下UNDOTBS01.DBF文件的大小

使用sys用戶登錄Oracle

方法一：重置表空間大小

linux下執(zhí)行

ALTER DATABASE DATAFILE '/opt/oracle/oradata/res/undotbs01.dbf' RESIZE 100M;

windows下執(zhí)行ALTER?DATABASE?DATAFILE?'E:\ORACLE\ORADATA\UNDOTBS01.DBF'?RESIZE?100M;

其中?'/opt/oracle/oradata/res/undotbs01.dbf' 為您系統(tǒng)中UNDOTBS01.DBF文件的存儲路徑;100M為重置后的大小，根據(jù)需要修改。

如果方法一無法執(zhí)行，可使用方法二

方法二：新建UNDO表空間，替換原來的UNDO表空間

1. 創(chuàng)建一個新的小的undo表空間

linux下執(zhí)行：

create?undo?tablespace?undotbs2?datafile?'/opt/oracle/oradata/res/undotbs02.dbf'?
size?100M?reuse?autoextend?on;
//‘/opt/oracle/oradata/res/undotbs02.dbf’為表空間文件想要存儲的位置,100M表示新的表空間的
初始大小并自動增長。

或

create?undo?tablespace?undotbs2?datafile?'/u01/app/oracle/oradata/orcl/undotbs02.dbf'?
size?100M?reuse?autoextend?on?next?5m?maxsize?1024M;
//‘/opt/oracle/oradata/res/undotbs02.dbf’為表空間文件想要存儲的位置。100M表示新的表空間的
初始大小，按5M增加最大到1G。

windows下執(zhí)行：

CREATE?UNDO?TABLESPACE?UNDOTBS2?DATAFILE?'E:\oradata\UNDOTBS02.DBF'?
SIZE?100M?REUSE?AUTOEXTEND?ON;

2. 設置新的表空間為系統(tǒng)的Undo表空間

--(1)動態(tài)更改spfile配置文件，設置新的表空間為系統(tǒng)undo_tablespace

alter?system?set?undo_tablespace=undotbs2;?

ALTER?SYSTEM?SET?UNDO_TABLESPACE=UNDOTBS2;

--(2)再次驗證數(shù)據(jù)庫的undo表空間(確定當前例程正在使用的UNDO表空間)??

show parameter undo_tablespace

--(3)等待原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';

--(4)查看當前是什么在使用這個回滾段???

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;

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該會話。??

--(6)kill session???

--(7)刪除舊的Undo表空間?

依舊使用sys用戶登錄，執(zhí)行?

drop tablespace undotbs1 including contents and datafiles;（drop?tablespace?undotbs1?including?contents;這個只刪除的表空間名沒有刪除掉數(shù)據(jù)文件的）

--(8)確認刪除是否成功；

select name from v$tablespace;

--(9)確定$ORACLE_HOME/dbs/spfileoinms.ora內(nèi)容是否發(fā)生變更：

$more spfileoinms.ora

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS2'

?

--(10)如果沒有發(fā)生變更請執(zhí)行如下語句：?

SQL> create pfile from spfile;?

File created.

--(11)刪除原UNDO表空間的數(shù)據(jù)文件，其文件名為步驟中執(zhí)行的結(jié)果。

#rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf

（雖然已經(jīng)刪除了系統(tǒng)所對應的undo表空間的數(shù)據(jù)文件，但用df -h查看，該系統(tǒng)空間不能釋放。? ?

主要是由于Oracle的一個進程在訪問該文件。可以kill Oracle訪問進程，或者重啟數(shù)據(jù)庫后，即可釋放系統(tǒng)的空間。）

3. 重啟數(shù)據(jù)庫

可以使用sys用戶，從sqlplus登錄，執(zhí)行startup force命令，強制重啟

若覺得強制重啟不安全的話，可以先shutdown immediate在啟動數(shù)據(jù)庫startup

至此，UNDOTBS01.DBF文件縮小完畢，如果想要還原原來的表空間名稱，重新執(zhí)行方法二即可。

4.切換回UNTOTBS1表空間

--(1)新建立UNDOTBS1表空間???

alter system set undo_tablespace=UNDOTBS1 scope=both;?

--(3)再次驗證數(shù)據(jù)庫的undo表空間? ?

show parameter undo_tablespace

?

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;

?

--(5)刪除? ?

--(6)確認刪除是否成功；

select name from v$tablespace;

方法三：禁止undo tablespace自動增長

alter?database?datafile?'/u01/app/oracle/oradata/orcl/undotbs01.dbf'?autoextend?off;

alter?database?datafile?'D:\app\Administrator\oradata\undotbs01.dbf'?autoextend?off;

?