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 / 1024from dba_data_fileswhere tablespace_name like 'UNDOTBS%';5. 查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。select s.username, u.namefrom v$transaction t, v$rollstat r, v$rollname u, v$session swhere s.taddr = t.addrand t.xidusn = r.usnand r.usn = u.usnorder by s.username;查询结果为空的话就能删除。6. 检查UNDO Segment状态;select usn,xacts,rssize / 1024 / 1024 / 1024,hwmsize / 1024 / 1024 / 1024,shrinksfrom v$rollstatorder by rssize;USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS800.000175476074218753.11521148681641700300.000236511230468753.22954559326172632000.000358581542968750.000358581542968750100.002067565917968753.04867553710938900.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 stringAUTOundo_retention integer900undo_tablespace stringUNDOTBS29. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE;select usn,xacts,status,rssize / 1024 / 1024,hwmsize / 1024 / 1024,shrinksfrom v$rollstatorder by rssize;select usn,xacts,status,rssize / 1024 / 1024,hwmsize / 1024 / 1024,shrinksfrom v$rollstatorder by rssize;select t.segment_name, t.tablespace_name, t.segment_id, t.statusfrom dba_rollback_segs t;SEGMENT_NAMETABLESPACE_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还原表空间所对应的回滚段均为OFFLINE10.到$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"}'
如果觉得《Oracle undo表空间爆满的解决》对你有帮助,请点赞、收藏,并留下你的观点哦!