如何处理Oracle的UNDO表空间所对应的数据文件过大

 

1 查看undo的表空间大小和最大值 select t.file_name,t.tablespace_name, t.bytes/1024/1024/1024 "GB", t.maxbytes/1024/1024/1024 "Max GB" from dba_data_files t where t.tablespace_name='UNDOTBS1' 数据文件为：/oracle/oradata/undo/undotbs01.dbf 2 创建一个新的undo表空间，用来替换原来的undo表空间 create undo tablespace UNDOTBS2 datafile '/oracle/oradata/log/undotbs02.dbf' size 10M autoextend on maxsize unlimited; 3 把新的undo表空间设置成数据库的undo表空间 alter system set undo_tablespace=UNDOTBS2 scope=both; 4 再次验证数据库的undo表空间 show parameter undo_tablespace 5 等待原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' 如果上面有状态online的对象，可以查询具体对象的sid，serial# 5.1 查看当前是什么在使用这个回滚段 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; --比如：对象为：sid 474,serial 6794 5.2 根据sid查出具体的sql 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该会话。 5.3 kill session alter system kill session '474,6794'; 5.4 删除原undo表空间及其系统的数据问题 drop tablespace UNDOTBS1 including contents and datafiles; （在AIX系统中，虽然已经删除了系统所对应的undo表空间的数据文件，但用df -g查看，该系统空间不能释放。 主要是由于Oracle的一个进程在访问该文件。可以kill Oracle访问进程，或者重启数据库后，即可释放系统的空间。） 6新建立UNDOTBS1表空间 create undo tablespace UNDOTBS1 datafile '/oracle/oradata/undo/undotbs01.dbf' size 10M autoextend on maxsize 12G; 7切换回UNTOTBS1 alter system set undo_tablespace=UNDOTBS1 scope=both; 8 等待UNDO表空间UNDOTBS2 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='UNDOTBS2' ORDER BY 5 DESC; 9 删除 drop tablespace UNDOTBS2 including contents and datafiles;