回收undo資料表空間

回收UNDO空間:
1確認undo
selectfile_name,bytes/1024/1024fromdba_data_fileswheretablespace_namelike'UNDOTBS1';
2檢查UNDOSegment狀態
3selectusn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinksfromv$rollstatorderbyrssize;
4建立新的UNDO資料表空間
createundotablespaceundotbs2datafile'/oradata/mlq/undotbs02.dbf'size20M;
5切換UNDO資料表空間為新的UNDO資料表空間
altersystemsetundo_tablespace=undotbs2scope=both;
6等待原UNDO資料表空間所有UNDOSEGMENTOFFLINE
selectusn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinksfromv$rollstatorderbyrssize;
7刪除原UNDO資料表空間
droptablespaceundotbs1includingcontents;
8檢查空間情況

selecttablespace_name,sum(bytes)/1024/1024/1024GBfromdba_data_filesgroupbytablespace_nameunionallselecttablespace_name,sum(bytes)/1024/1024/1024GBfromdba_temp_filesgroupbytablespace_nameorderbyGB;

本文出自 “mlqiang” 部落格，請務必保留此出處http://162234.blog.51cto.com/152234/1303270