第十一章： 管理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.
