产生问题的原因主要以下两点: 1. 有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况; 2. 有较大事务没有收缩或者没有提交所导制; $>exp vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log owner=vas rows=y indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000M 解决步骤: 查找数据库的UNDO表空间名 SQL> select name from v$tablespace; 检查数据库UNDO表空间占用空间情况以及数据文件存放位置; SQL>select file_name, bytes/1024/1024 from dba_data_files; 查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。 select s.username, u.name, d.tablespace_name from v$transaction t,v$rollstat r, v$rollname u, v$session s, dba_rollback_segs d where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn and r.usn=d.SEGMENT_ID order by s.username; 检查UNDO Segment状态 select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v$rollstat order by rssize; 创建新的UNDO表空间,并设置自动扩展参数; create undo tablespace undotbs2 datafile '/oradata/oradata/ddptest/UNDOTBS2.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited; 动态更改spfile配置文件; alter system set undo_tablespace=undotbs2 scope=both; 等待原UNDO表空间所有UNDO SEGMENT OFFLINE; select segment_name, tablespace_name, status from dba_rollback_segs; 再执行看UNDO表空间所有UNDO SEGMENT ONLINE; select segment_name, tablespace_name, status from dba_rollback_segs; 删除原有的UNDO表空间; drop tablespace undotbs2 including contents; 确认删除是否成功; select name from v$tablespace; 册除原UNDO表空间的数据文件,其文件名为步骤中执行的结果。 #rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf -- 查看事务对应的回滚段 select a.name, b.xacts, c.sid, c.serial# ,d.sql_text from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece; -- 查看事务占用undo 空间 col username for a16 col oSUSEr for a16 SELECT t.used_ublk, s.username, s.sid, s.serial#, pr.PID, s.OSUSER, s.MACHINE, s.PROGRAM, rs.segment_id, r.usn, rs.segment_name, r.rssize/1024/1024, sq.sql_text FROM v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs, v$sqltext sq, v$process pr WHERE s.saddr = t.ses_addr AND t.xidusn = r.usn AND rs.segment_id = t.xidusn AND s.sql_address=sq.address AND s.sql_hash_value = sq.hash_value AND s.PADDR=pr.ADDR ORDER BY t.used_ublk DESC, sq.PIECE; |
|
来自: 姑苏慕容凡 > 《Oracle 管理》