删除一个过大的UNDOTBS

select file_name,bytes/1024/1024 from dba_data_files where autoextensible='YES';

一般自动扩展最容易变得很大的就是untotbs
/oradata/rmdb/undotbs01.dbf     32G

处理过程：
(1) 生成autoextend off语句
select 'alter database datafile '''||file_name||''' AUTOEXTEND OFF;' from dba_data_files where autoextensible='YES';

(2) 新建一个undotbs
create undo tablespace undotbs1 datafile '/opt/oracle/oradata/ora11g/undotbs01.dbf' size 500m;

(3) 切换undotbs,并记录到spfile
alter system set undo_tablespace=UNDOTBS2 scope=both;

(4) 过几天再把undotbs1干掉
drop tablespace undotbs1 including contents and datafiles cascade constraints;

 

评论

1 楼 sblig 2011-08-19  

清理UNDO表空间

SQL>connsys/sss@sssdbas sysdba
SQL>create undo tablespace UNDOTBS2 datafile 'C:\oracle\oradata\sssdb\UNDOTBS02.DBF'size 1M extent management local;
SQL>alter system set undo_tablespace=UNDOTBS2;
SQL>drop tablespace UNDOTBS1 including contents;
SQL>shutdown immediate
SQL>host del C:\oracle\oradata\sssdb\UNDOTBS01.DBF;

如果删除文件失败，请重新启动计算机后再删除。