第十一章: 管理UNDO

   1、undo tablespace 功能?
        存放从datafiles 读出的数据块旧的镜像
       
       1)回滚事务:rollback
       2)读一致性:正在做DML操作的数据块,在没有提交前,其他用户不能读,其他用户读undo里面的数据块信息
       3)事务的恢复:instance recover(undo -------->rollback)
       4)倒叙查询 :flashback query、flashback table
   2、undo 的管理模式:
       1)manaual 手工:roll segment
       2)auto  自动:undo tablespace (init parameter :undo_management  = auto)
 
 
      
   3、undo 表空间管理
     1)建立新的undo表空间(处于active状态的undo tablespace 不能offline 和 drop),可以建立多个undo表空间,但一个时刻只有一个处于active
     09:47:08 SQL> create undo tablespace                                                                                                    
09:47:17   2    undo_tbs datafile '/u01/app/oracle/oradata/prod/undo_tbs01.dbf' size 100m                                               
09:47:20   3    autoextend on next 10m maxsize 500m;                                                                                    
  09:47:55 SQL> select tablespace_name,status ,contents from dba_tablespaces;                                                             

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
TEST                           ONLINE    PERMANENT
UNDO_TBS                       ONLINE    UNDO

8 rows selected.
    2)查看当前正在使用的undo tablespace
      09:48:00 SQL> show parameter undo                                                                                                       

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
09:48:56 SQL>
   3)切换undo
 09:50:10 SQL> alter system set undo_tablespace=undo_tbs;                                                                                

System altered.

09:50:28 SQL> show parameter undo                                                                                                       

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDO_TBS

    4)删除undo tablespace
    09:51:36 SQL>  drop tablespace undotbs1 including contents and datafiles;                                                               

Tablespace dropped.


    5) undo_retention 参数
   
    19:25:14 SQL> show parameter undo                                                                                                       

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      RTBS    
 
 SQL>Alter tablespace undotbs1 retention  guarantee;

如果想禁止undo表空间retention guarantee,
例如:
SQL> Alter tablespace undotbs1 retention noguarantee;

       当事务提交后,undo 数据块在undo tablespace 空间未使用完之前的的保留时间(不被覆盖)
   SQL> Alter tablespace undotbs1 retention guarantee;(保证在retention 期间不允许被覆盖)
    6)undo 表空间的大小
   
         1) undo_retention
         2) undo block /per second 
         3) UNDO BLOCK SIZE
 
 4、undo 信息的查询
 
       1) v$session 查看用户建立的session
       2) v$transaction  当前的事务
       3) v$rollname  回滚段的名称
       4) v$rollstat 回滚段的状态
      
       10:03:28 SQL> select username ,sid,serial# from v$session                                                                               
10:03:39   2    where username is not null;                                                                                             

USERNAME                              SID    SERIAL#
------------------------------ ---------- ----------
SCOTT                                 144          7
SYS                                   147         15
SCOTT                                 159         15

10:07:59 SQL> r                                                                                                                         
  1  select a.sid,a.serial#,a.username,b.xidusn,xidslot,b.ubablk,b.status,b.name from v$session a,v$transaction b
  2*  where a.saddr=b.ses_addr

10:07:59 SQL> col name for a10                                                                                                          
10:08:05 SQL> /                                                                                                                         

       SID    SERIAL# USERNAME                 XIDUSN    XIDSLOT     UBABLK STATUS           NAME
---------- ---------- -------------------- ---------- ---------- ---------- ---------------- ----------
       144          7 SCOTT                        15         26         76 ACTIVE

------------xidusn :undo segment 的id ,xidslot 事务槽的id


10:09:14 SQL> select * from v$rollname;                                                                                                 

       USN NAME
---------- ----------
         0 SYSTEM
        11 _SYSSMU11$
        12 _SYSSMU12$
        13 _SYSSMU13$
        14 _SYSSMU14$
        15 _SYSSMU15$
        16 _SYSSMU16$
        17 _SYSSMU17$
        18 _SYSSMU18$
        19 _SYSSMU19$
        20 _SYSSMU20$

11 rows selected.

----默认system有一个undo segment,undo tablespace 会被分配10个undo segment

10:11:13 SQL> select usn,EXTENTS,WRITES  ,XACTS  ,STATUS from v$rollstat;                                                               

       USN    EXTENTS     WRITES      XACTS STATUS
---------- ---------- ---------- ---------- ---------------
         0          6      27264          0 ONLINE
        11          2      35170          0 ONLINE
        12          2      27172          0 ONLINE
        13          2      42362          0 ONLINE
        14          2      30654          0 ONLINE
        15          2      13568          1 ONLINE
        16         21    5753978          0 ONLINE
        17          2      34292          0 ONLINE
        18          4     119852          1 ONLINE
        19          2      42584          0 ONLINE
        20          2      32172          0 ONLINE

11 rows selected.

10:15:32 SQL> col usn for a10                                                                                                           
10:15:48 SQL> r                                                                                                                         
  1  select a.sid,a.serial#,a.username,b.xidusn,xidslot,b.ubablk,b.status,c.usn,c.name,d.extents,d.writes,d.xacts
  2    from v$session a,v$transaction b,v$rollname c ,v$rollstat d
  3*     where a.saddr=b.ses_addr and b.xidusn=c.usn and c.usn=d.usn;
 
        
  
10:15:54 SQL> /                                                                                                                         

       SID    SERIAL# USERNAME       XIDUSN    XIDSLOT     UBABLK STATUS            USN NAME          EXTENTS     WRITES
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     XACTS
----------
       144          7 SCOTT              15         26         76 ACTIVE             15 _SYSSMU15$          2      13568
         1

       143         41 SYS                18         34        171 ACTIVE             18 _SYSSMU18$          4     119852
         1


查看undo segment 数据块状态 (dba_undo_EXTENTS)

10:03:10 SQL> insert into t01 values (6) ;                                                                                              

1 row created.

10:05:09 SQL> insert into t01 values (7) ;                                                                                              

1 row created.

10:05:10 SQL

10:12:32 SQL> create table emp1 as select * from scott.emp;                                                                             

Table created.

10:12:52 SQL> insert into emp1 select * from emp1;                                                                                      

14 rows created.

10:18:10 SQL> select segment_name,tablespace_name,extent_id,file_id ,bytes/1024 ,status from dba_undo_extents                           
10:18:45   2    where status like '%ACTIVE%';                                                                                           

SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID BYTES/1024 STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
_SYSSMU18$                     UNDO_TBS                                2          7         64 ACTIVE
_SYSSMU15$                     UNDO_TBS                                0          7         64 ACTIVE

10:12:59 SQL> COMMIT;                                                                                                                   

Commit complete.

10:19:01 SQL> /                                                                                                                         

SEGMENT_NAME                   TABLESPACE_NAME                 EXTENT_ID    FILE_ID BYTES/1024 STATUS
------------------------------ ------------------------------ ---------- ---------- ---------- ----------
_SYSSMU15$                     UNDO_TBS                                0          7         64 ACTIVE

10:20:01 SQL>
查看undo tablespace 统计信息

10:23:20 SQL> select BEGIN_TIME,end_time,undotsn,undoblks,ACTIVEBLKS,EXPIREDBLKS,MAXQUERYID from v$undostat;                            

BEGIN_TIME          END_TIME               UNDOTSN   UNDOBLKS ACTIVEBLKS EXPIREDBLKS MAXQUERYID
------------------- ------------------- ---------- ---------- ---------- ----------- -------------
2011-08-08 10:22:35 2011-08-08 10:23:20          8          0        160         760
2011-08-08 10:12:35 2011-08-08 10:22:35          8          6        160         760
2011-08-08 10:02:35 2011-08-08 10:12:35          8        589        160           8
2011-08-08 09:52:35 2011-08-08 10:02:35          8        179        160           8
2011-08-08 09:42:35 2011-08-08 09:52:35          8         19        160           0
2011-08-08 09:32:35 2011-08-08 09:42:35          1          7        160        2840
2011-08-08 09:22:35 2011-08-08 09:32:35          1          2        160        2848
2011-08-08 09:12:35 2011-08-08 09:22:35          1          0          0           0
2011-08-08 09:02:35 2011-08-08 09:12:35          1          3          0           0

9 rows selected.

 

04:27:39 SQL> select segment_name,tablespace_name ,segment_id,file_id ,status from dba_rollback_segs;                                   

SEGMENT_NAME                   TABLESPACE_NAME                SEGMENT_ID    FILE_ID STATUS
------------------------------ ------------------------------ ---------- ---------- ----------------
SYSTEM                         SYSTEM                                  0          1 ONLINE
_SYSSMU1$                      RTBS                                    1          2 ONLINE
_SYSSMU2$                      RTBS                                    2          2 ONLINE
_SYSSMU3$                      RTBS                                    3          2 ONLINE
_SYSSMU4$                      RTBS                                    4          2 ONLINE
_SYSSMU5$                      RTBS                                    5          2 ONLINE
_SYSSMU6$                      RTBS                                    6          2 ONLINE
_SYSSMU7$                      RTBS                                    7          2 ONLINE
_SYSSMU8$                      RTBS                                    8          2 ONLINE
_SYSSMU9$                      RTBS                                    9          2 ONLINE
_SYSSMU10$                     RTBS                                   10          2 ONLINE

11 rows selected.