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;