ORACLE在线切换undo表空间

最新推荐文章于 2023-01-19 10:59:28 发布

yrg5101

最新推荐文章于 2023-01-19 10:59:28 发布

阅读量226

点赞数

分类专栏： oracle基本知识文章标签： oracle transactions sun sql

oracle基本知识专栏收录该内容

75 篇文章 0 订阅

订阅专栏

切换undo的一些步骤和基本原则

 
        查看原undo相关参数 
       
        SHOW PARAMETER UNDO; 
       
        创建新undo空间 
       
        create 
         undo tablespace undo_x datafile  
        'E:\ORACLE\ORADATA\XIFENFEI\undo_xifenfei.dbf' 
         size 
         10M 
       
        autoextend  
        on 
         next 
         10M maxsize 30G; 
       
        查询历史undo是否还有事务(包含回滚事务) 
       
        SELECT 
         a.tablespace_name,a.segment_name,b.ktuxesta,b.ktuxecfl, 
       
        b.ktuxeusn|| 
        '.' 
        ||b.ktuxeslt|| 
        '.' 
        ||b.ktuxesqn trans 
       
        FROM 
         dba_rollback_segs a, x$ktuxe b 
       
        WHERE 
         a.segment_id = b.ktuxeusn 
       
        AND 
         a.tablespace_name =  
        UPPER 
        ( 
        '&tsname' 
        ) 
       
        AND 
         b.ktuxesta <>  
        'INACTIVE' 
        ; 
       
        --因为有undo_retention参数,所以不能简单的通过确定该sql无事务就可以删除原undo 
       
        切换undo表空间(无论是否有事务,均可以切换[最好是无事务时切换],但是不能直接删除原undo表空间) 
       
        alter 
         system  
        set 
         undo_tablespace= 
        'undo_x' 
        ; 
       
        alert日志现象,表明原undo还有事务 
       
        Sun Jun 17 20:10:45 2012 
       
        Successfully onlined Undo Tablespace 7. 
       
        [36428] **** active transactions found  
        in 
         undo Tablespace 2 - moved  
        to 
         Pending Switch- 
        Out 
         state. 
       
        [36428] active transactions found/affinity dissolution incompletein undo tablespace 2 during switch- 
        out 
        . 
       
        ALTER 
         SYSTEM  
        SET 
         undo_tablespace= 
        'undo_xifenfei' 
         SCOPE=BOTH; 
       
        Sun Jun 17 20:11:38 2012 
       
        [36312] **** active transactions found  
        in 
         undo Tablespace 2 - moved  
        to 
         Pending Switch- 
        Out 
         state. 
       
        Sun Jun 17 20:16:15 2012 
       
        [36312] **** active transactions found  
        in 
         undo Tablespace 2 - moved  
        to 
         Pending Switch- 
        Out 
         state. 
       
        --只能表明有事务,就算长时间未出现类似记录,不能证明一定可以删除原undo,因为undo_retention 
       
        查询回滚段情况(原undo表空间的回滚段全部offline,可以删除相关表空间) 
       
        select 
         tablespace_name,segment_name,status  
        from 
         dba_rollback_segs; 
       
        离线原undo表空间 
       
        alter 
         tablespace undotbs1 offline; 
       
        确定原undo回滚段全部offline,直接删除 
       
        drop 
         tablespace undotbs1 including contents  
        and 
         datafiles;