UNDOTBS01.DBF是什么文件?
表空间文件,主要就是为了undo,即撤销、回滚,只要有DML操作等,就会产生undo数据,久而久之会变得非常大,占用大量磁盘空间。这里要说的是怎么消除这种占用。
查询undo表空间的使用
SELECT a.tablespace_name as tablespace_name,
to_char(b.total/1024/1024,999999.99) as Total,
to_char((b.total-a.free)/1024/1024,999999.99) as Used,
to_char(a.free/1024/1024,999999.99) as Free,
to_char(round((total-free)/total,4)*100,999.99) as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
AND a.tablespace_name='UNDOTBS_NEW'
ORDER BY a.tablespace_name;
处理方式
处理方式1:扩大undo tablespace的大小
alter database datafile 'D:\app\Administrator\oradata\kisbidb\undotbs01.dbf' resize 10240M;
处理方式2:缩小undo tablespace
--(1)创建一个新的小空间的undo tablespace
create undo tablespace undotBS2 datafile 'D:\app\Administrator\oradata\kisbidb\undotbs02.dbf' size 5m;
--(2)设置新的表空间为系统undo_tablespace
alter system set undo_tablespace=undotBS2;
--(3)删除旧的表空间
drop tablespace undotbs1 including contents;
处理方式3:禁止undo tablespace自动增长
alter database datafile 'D:\app\Administrator\oradata\kisbidb\undotbs01.dbf' autoextend off;