ORACLE UNDO表空间回收处理

来源：互联网发布：java 执行垃圾回收编辑：程序博客网时间：2024/05/17 11:32

目前有一个10g的数据库在检查时发现undo表空间异常庞大，undo表空间占了将近45G

1、首先查询当前undo表空间的使用情况，有文件使用已经达到32G

SQL> select file_name,bytes/1024/1024 MB,autoextensible from dba_data_files where tablespace_name like 'UNDOTBS1';FILE_NAME                          MB    AUT---------------------------------------- ----------   ---D:\ORADB\ORADATA\ORCL\UNDOTBS01.DBF     32767.9844 YESD:\ORADB\ORADATA\ORCL\UNDOTBS02.DBF         8192 YESD:\ORADB\ORADATA\ORCL\UNDOTBS03.DBF         4096 YES

2、查看当前undo的参数

SQL> show parameter undo;NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS1

3、采取重建undo表空间的方法来，取消自动扩展

create undo tablespace undotbs2 datafile 'D:\ORADB\ORADATA\ORCL\UNDOTBS201.DBF' size 4G autoextend off;alter tablespace undotbs2 add datafile 'D:\ORADB\ORADATA\ORCL\UNDOTBS202.DBF' size 4G autoextend off ;alter tablespace undotbs2 add datafile 'D:\ORADB\ORADATA\ORCL\UNDOTBS203.DBF' size 4G autoextend off;

4、切换undo表空间

SQL>alter system set undo_tablespace=undotbs2 scope=both;

SQL> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     900undo_tablespace                      string      UNDOTBS2

5、查看当前的undo1 的回滚段状态

SQL> select segment_name,tablespace_name,status from dba_rollback_segs t where tablespace_name='UNDOTBS1' and status='OLINE';SEGMENT_NAME                   TABLESPACE_NAME                STATUS------------------------------ ------------------------------ ----------------_SYSSMU9$                      UNDOTBS1                       ONLINE_SYSSMU10$                     UNDOTBS1                       ONLINE

6、由于上面的两个回滚段一直处于online状态，无法直接删除undotbs1表空间和数据文件回收空间，首先试着使用命令下线回滚段

SQL>  ALTER ROLLBACK SEGMENT "_SYSSMU9$" OFFLINE;Rollback segment altered.

7、查询回滚段状态，一直处于pengding offline状态

SQL> SELECT s.username, u.name ,r.status 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;USERNAME                    NAME                           STATUS--------------------------- ------------------------------ ---------------u01                         _SYSSMU10$                     PENDING OFFLINEu02                         _SYSSMU505$                    ONLINEu02                         _SYSSMU501$                    ONLINEu02                         _SYSSMU9$                      PENDING OFFLINEu02                         _SYSSMU497$                    ONLINEu02                         _SYSSMU506$                    ONLINE6 rows selected.

8、查找到相关的事务ID

SQL> SELECT s.sid,serial#, u.name ,r.status  2  FROM v$transaction t, v$rollstat r, v$rollname u, v$session s  3  WHERE s.taddr = t.addr AND t.xidusn = r.usn AND r.usn = u.usn and r.status='PENDING OFFLINE' ;       SID    SERIAL# NAME                           STATUS---------- ---------- ------------------------------ ---------------      3150      18393 _SYSSMU9$                      PENDING OFFLINE      3175       9695 _SYSSMU10$                     PENDING OFFLINE

9、根据上面的SID号查询该会话正在执行的操作

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;

10、结束该会话

  alter system kill session '3150,18393';  alter system kill session '3175,9695'; ALTER ROLLBACK SEGMENT "_SYSSMU9$" OFFLINE;  ALTER ROLLBACK SEGMENT "_SYSSMU10$" OFFLINE;

11、验证当前undotbs1 是否全部offline

select * from dba_rollback_segs t where tablespace_name='UNDOTBS1' and status='ONLINE';

12、删除掉undotbs1

SQL> drop tablespace undotbs1 including contents and datafiles;Tablespace dropped.

SQL> SELECT   tablespace_name, status, SUM (bytes) / 1024 / 1024 "Bytes(M)"  2      FROM   dba_undo_extents  3  GROUP BY   tablespace_name, status;TABLESPACE_NAME                STATUS      Bytes(M)------------------------------ --------- ----------UNDOTBS2                       UNEXPIRED    29.8125UNDOTBS2                       EXPIRED      50.1875UNDOTBS2                       ACTIVE             3

阅读全文

0 0