Oracle undo表空间爆满的解决

1. 启动SQLPLUS，并用sys登陆到数据库。#su - oracle  $>sqlplus / as sysdba 2. 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间：Show parameter undo_tablespace。3. 确认UNDO表空间；SQL> select name from v$tablespace;  NAME  ------------------------------  UNDOTBS1 4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置；select file_name, bytes / 1024 / 1024 / 1024  from dba_data_files where tablespace_name like 'UNDOTBS%';5. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。select s.username, u.name  from v$transaction t, v$rollstat r, v$rollname u, v$session s where s.taddr = t.addr   and t.xidusn = r.usn   and r.usn = u.usn order by s.username;查询结果为空的话就能删除。6. 检查UNDO Segment状态；select usn,       xacts,       rssize / 1024 / 1024 / 1024,       hwmsize / 1024 / 1024 / 1024,       shrinks  from v$rollstat order by rssize;USN  XACTS  RSSIZE/1024/1024/1024  HWMSIZE/1024/1024/1024  SHRINKS800.000175476074218753.11521148681641700300.000236511230468753.22954559326172632000.000358581542968750.000358581542968750100.002067565917968753.048675537109389201000.002067565917968750.648170471191406819700.002311706542968753.94835662841797730400.003044128417968752.000114440917976511100.006950378417968752.26921844482422740900.007926940917968752.07530975341797773600.007926940917968751.31906890869141775200.008903503417968753.13677215576172699501.968338012695313.99906921386719267这还原表空间中还存在12个回滚的对象。7. 创建新的UNDO表空间，并设置自动扩展参数；create undo tablespace undotbs2 datafile '/u02/pnrdb/undotbs2_01.dbf' size 100m reuse autoextend on next 100m maxsize unlimited;8. 切换UNDO表空间为新的UNDO表空间 ， 动态更改spfile配置文件；alter system set undo_tablespace=undotbs2 scope=both;  9.验证当前数据库的 UNDO表空间SQL> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- --------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS29. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE；select usn,       xacts,       status,       rssize / 1024 / 1024,       hwmsize / 1024 / 1024,       shrinks  from v$rollstat order by rssize;select usn,       xacts,       status,       rssize / 1024 / 1024,       hwmsize / 1024 / 1024,       shrinks  from v$rollstat order by rssize;select t.segment_name, t.tablespace_name, t.segment_id, t.status  from dba_rollback_segs t;SEGMENT_NAME      TABLESPACE_NAME SEGMENT_ID   STATUS_SYSSMU1$UNDOTBS11OFFLINE_SYSSMU2$UNDOTBS12OFFLINE_SYSSMU3$UNDOTBS13OFFLINE_SYSSMU4$UNDOTBS14OFFLINE_SYSSMU5$UNDOTBS15OFFLINE_SYSSMU6$UNDOTBS16OFFLINE_SYSSMU7$UNDOTBS17OFFLINE_SYSSMU8$UNDOTBS18OFFLINE_SYSSMU9$UNDOTBS19OFFLINE_SYSSMU10$UNDOTBS110OFFLINE_SYSSMU11$UNDOTBS111OFFLINE_SYSSMU12$UNDOTBS112OFFLINE_SYSSMU13$UNDOTBS113OFFLINE_SYSSMU14$UNDOTBS114OFFLINE_SYSSMU15$UNDOTBS115OFFLINE_SYSSMU16$UNDOTBS116OFFLINE_SYSSMU17$UNDOTBS117OFFLINE_SYSSMU18$UNDOTBS118OFFLINE_SYSSMU19$UNDOTBS119OFFLINE_SYSSMU20$UNDOTBS120OFFLINE_SYSSMU21$UNDOTBS121OFFLINE_SYSSMU22$UNDOTBS122OFFLINE_SYSSMU23$UNDOTBS123OFFLINE_SYSSMU24$UNDOTBS124OFFLINE_SYSSMU25$UNDOTBS125OFFLINE_SYSSMU26$UNDOTBS126OFFLINE_SYSSMU27$UNDOTBS127OFFLINE_SYSSMU28$UNDOTBS128OFFLINE_SYSSMU29$UNDOTBS129OFFLINE_SYSSMU30$UNDOTBS130OFFLINE_SYSSMU31$UNDOTBS131OFFLINE_SYSSMU32$UNDOTBS132OFFLINE_SYSSMU33$UNDOTBS133OFFLINE_SYSSMU34$UNDOTBS134OFFLINE_SYSSMU35$UNDOTBS135OFFLINE上面对应的UNDOTBS1还原表空间所对应的回滚段均为OFFLINE 10.到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下内容是否发生变更：#cat $ORACLE_HOME/dbs/initddptest.ora……*.undo_management=’AUTO’*.undo_retention=10800*.undo_tablespace=’UNDOTBS2’……如果没有发生变更请执行如下语句：SQL> create pfile from spfile;File created.11. 删除原有的UNDO表空间；drop tablespace undotbs1 including contents and datafiles;12. os级别释放undo数据文件；到root下执行lsof |grep /u02/pnrdb/undotbs01.dbflsof |grep /u02/pnrdb/undotbs01.dbf |awk '{printf"kill -9 "$2"\n"}'