Managing Undo Data

Summary

  • Configuration Automaitc Undo Management
    • 将undo_management设置为auto
    • 设置当前的undo tablespace
  • Create an UNDO tablespace
  • Properly size an UND tablespace
  • Obtain undo segment information

little demo

查看一个事务占用了多少undo block?

SQL> create table t2 as select * from all_objects where 1=0;

Table created.

SQL> select * from t2;

no rows selected

SQL> desc t2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 OBJECT_NAME                               NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                 NOT NULL NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                   NOT NULL DATE
 LAST_DDL_TIME                             NOT NULL DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                 NOT NULL NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> insert into t2 select * from all_objects;

56374 rows created.

SQL> 
1、创建表及插入测试数据;
SQL> show user
USER is "SYS"
SQL> select addr,used_ublk from v$transaction;

ADDR              USED_UBLK
---------------- ----------
00000000776652E0         28

SQL> 
查看这个事务占用了多少undo block

如何设计规划undo表空间的大小?

SQL> select end_time,begin_time,undoblks from v$undostat;

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 13:00:27 2013/07/16 12:51:53        104
2013/07/16 12:51:53 2013/07/16 12:41:53         14
2013/07/16 12:41:53 2013/07/16 12:31:53         15
2013/07/16 12:31:53 2013/07/16 12:21:53         14
2013/07/16 12:21:53 2013/07/16 12:11:53         17
2013/07/16 12:11:53 2013/07/16 12:01:53         12
2013/07/16 12:01:53 2013/07/16 11:51:53        160
2013/07/16 11:51:53 2013/07/16 11:41:53         13
2013/07/16 11:41:53 2013/07/16 11:31:53         25
2013/07/16 11:31:53 2013/07/16 11:21:53         12
2013/07/16 11:21:53 2013/07/16 11:11:53         17

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 11:11:53 2013/07/16 11:01:53         15
2013/07/16 11:01:53 2013/07/16 10:51:53         97
2013/07/16 10:51:53 2013/07/16 10:41:53         17
2013/07/16 10:41:53 2013/07/16 10:31:53         12
2013/07/16 10:31:53 2013/07/16 10:21:53         15
2013/07/16 10:21:53 2013/07/16 10:11:53         18
2013/07/16 10:11:53 2013/07/16 10:01:53         12
2013/07/16 10:01:53 2013/07/16 09:51:53        121
2013/07/16 09:51:53 2013/07/16 09:41:53         16
2013/07/16 09:41:53 2013/07/16 09:31:53         18
2013/07/16 09:31:53 2013/07/16 09:21:53         19

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 09:21:53 2013/07/16 09:11:53         13
2013/07/16 09:11:53 2013/07/16 09:01:53         18
2013/07/16 09:01:53 2013/07/16 08:51:53         83
2013/07/16 08:51:53 2013/07/16 08:41:53         16
2013/07/16 08:41:53 2013/07/16 08:31:53         14
2013/07/16 08:31:53 2013/07/16 08:21:53         13
2013/07/16 08:21:53 2013/07/16 08:11:53         13
2013/07/16 08:11:53 2013/07/16 08:01:53         11
2013/07/16 08:01:53 2013/07/16 07:51:53        109
2013/07/16 07:51:53 2013/07/16 07:41:53         13
2013/07/16 07:41:53 2013/07/16 07:31:53         14

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 07:31:53 2013/07/16 07:21:53         21
2013/07/16 07:21:53 2013/07/16 07:11:53         14
2013/07/16 07:11:53 2013/07/16 07:01:53         13
2013/07/16 07:01:53 2013/07/16 06:51:53         91
2013/07/16 06:51:53 2013/07/16 06:41:53         12
2013/07/16 06:41:53 2013/07/16 06:31:53         16
2013/07/16 06:31:53 2013/07/16 06:21:53         15
2013/07/16 06:21:53 2013/07/16 06:11:53         16
2013/07/16 06:11:53 2013/07/16 06:01:53         13
2013/07/16 06:01:53 2013/07/16 05:51:53        101
2013/07/16 05:51:53 2013/07/16 05:41:53         13

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 05:41:53 2013/07/16 05:31:53         14
2013/07/16 05:31:53 2013/07/16 05:21:53         18
2013/07/16 05:21:53 2013/07/16 05:11:53         12
2013/07/16 05:11:53 2013/07/16 05:01:53         10
2013/07/16 05:01:53 2013/07/16 04:51:53        111
2013/07/16 04:51:53 2013/07/16 04:41:53         15
2013/07/16 04:41:53 2013/07/16 04:31:53         14
2013/07/16 04:31:53 2013/07/16 04:21:53         12
2013/07/16 04:21:53 2013/07/16 04:11:53         19
2013/07/16 04:11:53 2013/07/16 04:01:53         16
2013/07/16 04:01:53 2013/07/16 03:51:53         92

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 03:51:53 2013/07/16 03:41:53         15
2013/07/16 03:41:53 2013/07/16 03:31:53         14
2013/07/16 03:31:53 2013/07/16 03:21:53         14
2013/07/16 03:21:53 2013/07/16 03:11:53         15
2013/07/16 03:11:53 2013/07/16 03:01:53         15
2013/07/16 03:01:53 2013/07/16 02:51:53         93
2013/07/16 02:51:53 2013/07/16 02:41:53         11
2013/07/16 02:41:53 2013/07/16 02:31:53         17
2013/07/16 02:31:53 2013/07/16 02:21:53         15
2013/07/16 02:21:53 2013/07/16 02:11:53         22
2013/07/16 02:11:53 2013/07/16 02:01:53         12

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 02:01:53 2013/07/16 01:51:53        107
2013/07/16 01:51:53 2013/07/16 01:41:53         13
2013/07/16 01:41:53 2013/07/16 01:31:53         12
2013/07/16 01:31:53 2013/07/16 01:21:53         15
2013/07/16 01:21:53 2013/07/16 01:11:53         14
2013/07/16 01:11:53 2013/07/16 01:01:53         31
2013/07/16 01:01:53 2013/07/16 00:51:53        252
2013/07/16 00:51:53 2013/07/16 00:41:53         15
2013/07/16 00:41:53 2013/07/16 00:31:53         13
2013/07/16 00:31:53 2013/07/16 00:21:53         13
2013/07/16 00:21:53 2013/07/16 00:11:53         16

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/16 00:11:53 2013/07/16 00:01:53         21
2013/07/16 00:01:53 2013/07/15 23:51:53        110
2013/07/15 23:51:53 2013/07/15 23:41:53         14
2013/07/15 23:41:53 2013/07/15 23:31:53         13
2013/07/15 23:31:53 2013/07/15 23:21:53         18
2013/07/15 23:21:53 2013/07/15 23:11:53         18
2013/07/15 23:11:53 2013/07/15 23:01:53         16
2013/07/15 23:01:53 2013/07/15 22:51:53        118
2013/07/15 22:51:53 2013/07/15 22:41:53         14
2013/07/15 22:41:53 2013/07/15 22:31:53         14
2013/07/15 22:31:53 2013/07/15 22:21:53         19

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/15 22:21:53 2013/07/15 22:11:53         17
2013/07/15 22:11:53 2013/07/15 22:01:53         28
2013/07/15 22:01:53 2013/07/15 21:51:53       1253
2013/07/15 21:51:53 2013/07/15 21:41:53         17
2013/07/15 21:41:53 2013/07/15 21:31:53         10
2013/07/15 21:31:53 2013/07/15 21:21:53         13
2013/07/15 21:21:53 2013/07/15 21:11:53         16
2013/07/15 21:11:53 2013/07/15 21:01:53         12
2013/07/15 21:01:53 2013/07/15 20:51:53         89
2013/07/15 20:51:53 2013/07/15 20:41:53         14
2013/07/15 20:41:53 2013/07/15 20:31:53         13

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/15 20:31:53 2013/07/15 20:21:53         12
2013/07/15 20:21:53 2013/07/15 20:11:53         15
2013/07/15 20:11:53 2013/07/15 20:01:53         14
2013/07/15 20:01:53 2013/07/15 19:51:53         94
2013/07/15 19:51:53 2013/07/15 19:41:53         17
2013/07/15 19:41:53 2013/07/15 19:31:53         13
2013/07/15 19:31:53 2013/07/15 19:21:53         16
2013/07/15 19:21:53 2013/07/15 19:11:53         18
2013/07/15 19:11:53 2013/07/15 19:01:53         14
2013/07/15 19:01:53 2013/07/15 18:51:53         93
2013/07/15 18:51:53 2013/07/15 18:41:53         14

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/15 18:41:53 2013/07/15 18:31:53         12
2013/07/15 18:31:53 2013/07/15 18:21:53         19
2013/07/15 18:21:53 2013/07/15 18:11:53         16
2013/07/15 18:11:53 2013/07/15 18:01:53         11
2013/07/15 18:01:53 2013/07/15 17:51:53         95
2013/07/15 17:51:53 2013/07/15 17:41:53         12
2013/07/15 17:41:53 2013/07/15 17:31:53         13
2013/07/15 17:31:53 2013/07/15 17:21:53         14
2013/07/15 17:21:53 2013/07/15 17:11:53         14
2013/07/15 17:11:53 2013/07/15 17:01:53         10
2013/07/15 17:01:53 2013/07/15 16:51:53        118

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/15 16:51:53 2013/07/15 16:41:53         11
2013/07/15 16:41:53 2013/07/15 16:31:53         17
2013/07/15 16:31:53 2013/07/15 16:21:53         16
2013/07/15 16:21:53 2013/07/15 16:11:53         19
2013/07/15 16:11:53 2013/07/15 16:01:53         12
2013/07/15 16:01:53 2013/07/15 15:51:53         92
2013/07/15 15:51:53 2013/07/15 15:41:53         16
2013/07/15 15:41:53 2013/07/15 15:31:53         13
2013/07/15 15:31:53 2013/07/15 15:21:53         13
2013/07/15 15:21:53 2013/07/15 15:11:53         15
2013/07/15 15:11:53 2013/07/15 15:01:53         14

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/15 15:01:53 2013/07/15 14:51:53        109
2013/07/15 14:51:53 2013/07/15 14:41:53         14
2013/07/15 14:41:53 2013/07/15 14:31:53         14
2013/07/15 14:31:53 2013/07/15 14:21:53         12
2013/07/15 14:21:53 2013/07/15 14:11:53         15
2013/07/15 14:11:53 2013/07/15 14:01:53         12
2013/07/15 14:01:53 2013/07/15 13:51:53         94
2013/07/15 13:51:53 2013/07/15 13:41:53         18
2013/07/15 13:41:53 2013/07/15 13:31:53         12
2013/07/15 13:31:53 2013/07/15 13:21:53         17
2013/07/15 13:21:53 2013/07/15 13:11:53         14

END_TIME            BEGIN_TIME            UNDOBLKS
------------------- ------------------- ----------
2013/07/15 13:11:53 2013/07/15 13:01:53         12
2013/07/15 13:01:53 2013/07/15 12:51:53        101
2013/07/15 12:51:53 2013/07/15 12:41:53         16
2013/07/15 12:41:53 2013/07/15 12:31:53         12
2013/07/15 12:31:53 2013/07/15 12:21:53         15
2013/07/15 12:21:53 2013/07/15 12:11:53         16
2013/07/15 12:11:53 2013/07/15 12:01:53         87
2013/07/15 12:01:53 2013/07/15 11:51:53         22

151 rows selected.

SQL> 
v$undostat视图,记录了undo表空间的历史数据,据此可以设计undo表空间的大小.

查询undo tablespace

查看数据库有哪些表空间?

SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
APP_FGPS                       ONLINE    PERMANENT
RMAN_TS                        ONLINE    PERMANENT
MYUNDOTBS                      ONLINE    UNDO
TESTUNDOTBS                    ONLINE    UNDO

10 rows selected.
View dba_tablespaces;

查看数据库有哪些表空间文件? 

SQL> select name,status from v$datafile;

NAME                                                         STATUS
------------------------------------------------------------ -------
/u01/app/oracle/oradata/testdb/system01.dbf                  SYSTEM
/u01/app/oracle/oradata/testdb/sysaux01.dbf                  ONLINE
/u01/app/oracle/oradata/testdb/undotbs01.dbf                 ONLINE
/u01/app/oracle/oradata/testdb/users01.dbf                   ONLINE
/u01/app/oracle/oradata/testdb/example01.dbf                 ONLINE
/u01/app/oracle/oradata/testdb/DATACENTER01.dbf              ONLINE
/u01/app/oracle/oradata/testdb/rman_ts01.dbf                 ONLINE
/u01/app/oracle/oradata/testdb/myundotbs01.dbf               ONLINE
/u01/app/oracle/oradata/testdb/testundotbs01.dbf             ONLINE

9 rows selected.
View v$datafile;

查看当前系统正在使用哪个undo tablespace? 

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      MYUNDOTBS
View parameter;

查询指定undo tablespace有哪些undo segments?

SQL> select segment_name,tablespace_name from dba_rollback_segs where tablespace_name='UNDOTBS1';

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
_SYSSMU1_1240252155$           UNDOTBS1
_SYSSMU2_111974964$            UNDOTBS1
_SYSSMU3_4004931649$           UNDOTBS1
_SYSSMU4_1126976075$           UNDOTBS1
_SYSSMU5_4011504098$           UNDOTBS1
_SYSSMU6_3654194381$           UNDOTBS1
_SYSSMU7_4222772309$           UNDOTBS1
_SYSSMU8_3612859353$           UNDOTBS1
_SYSSMU9_3945653786$           UNDOTBS1
_SYSSMU10_3271578125$          UNDOTBS1

10 rows selected.
View Code

创建undo tablespace

SQL> create undo tablespace testundotbs datafile '/u01/app/oracle/oradata/testdb/testundotbs01.dbf' size 2M;

Tablespace created.

SQL> 
1、创建undo表空间
SQL> create undo tablespace testundotbs datafile '/u01/app/oracle/oradata/testdb/testundotbs01.dbf' size 2M autoextend off;

Tablespace created.

SQL> 
创建undo表空间,并且不扩展;

删除undo tablespace

SQL> drop tablespace testundotbs;

Tablespace dropped.
逻辑删除undo表空间;
11203ora-> ll
total 2618412
-rw-r----- 1 oracle oinstall   9814016 Jul 16 16:27 control01.ctl
-rw-r----- 1 oracle oinstall 209723392 Jul 16 16:05 DATACENTER01.dbf
-rw-r----- 1 oracle oinstall 362422272 Jul 16 16:05 example01.dbf
-rw-r----- 1 oracle oinstall 120594432 Jul 16 16:26 myundotbs01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 15 22:00 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 16 16:00 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 16 16:26 redo03.log
-rw-r----- 1 oracle oinstall 209723392 Jul 16 16:05 rman_ts01.dbf
-rw-r----- 1 oracle oinstall 671096832 Jul 16 16:26 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 Jul 16 16:26 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Jul 16 16:18 temp01.dbf
-rw-r----- 1 oracle oinstall   2105344 Jul 16 16:26 testundotbs01.dbf
-rw-r----- 1 oracle oinstall 120594432 Jul 16 16:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall  23601152 Jul 16 16:05 users01.dbf
11203ora-> rm -rf testundotbs01.dbf 
11203ora-> ll
total 2616356
-rw-r----- 1 oracle oinstall   9814016 Jul 16 16:27 control01.ctl
-rw-r----- 1 oracle oinstall 209723392 Jul 16 16:05 DATACENTER01.dbf
-rw-r----- 1 oracle oinstall 362422272 Jul 16 16:05 example01.dbf
-rw-r----- 1 oracle oinstall 120594432 Jul 16 16:26 myundotbs01.dbf
-rw-r----- 1 oracle oinstall  52429312 Jul 15 22:00 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Jul 16 16:00 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Jul 16 16:26 redo03.log
-rw-r----- 1 oracle oinstall 209723392 Jul 16 16:05 rman_ts01.dbf
-rw-r----- 1 oracle oinstall 671096832 Jul 16 16:26 sysaux01.dbf
-rw-r----- 1 oracle oinstall 765468672 Jul 16 16:26 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Jul 16 16:18 temp01.dbf
-rw-r----- 1 oracle oinstall 120594432 Jul 16 16:05 undotbs01.dbf
-rw-r----- 1 oracle oinstall  23601152 Jul 16 16:05 users01.dbf
11203ora-> 
物理删除undo tablespace;
SQL> drop tablespace testundotbs including contents and datafiles;

Tablespace dropped.

SQL> 
逻辑删除、物理删除undo tablespace;

 

切换undo tablespace 

SQL> alter system set undo_tablespace=testundotbs;

System altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      TESTUNDOTBS
SQL> 
切换undo tablespace

little demo 

将undo tablespace设置很小(2M),往表中插入超过2M的数据,会出现什么结果?

result:ora-30036

SQL> create undo tablespace testundotbs datafile '/u01/app/oracle/oradata/testdb/testundotbs01.dbf' size 2M autoextend off;

Tablespace created.
create undo tablespace
SQL> select tablespace_name,status,contents from dba_tablespaces;

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
EXAMPLE                        ONLINE    PERMANENT
APP_FGPS                       ONLINE    PERMANENT
RMAN_TS                        ONLINE    PERMANENT
MYUNDOTBS                      ONLINE    UNDO
TESTUNDOTBS                    ONLINE    UNDO

10 rows selected.
View undo tablespace
SQL> alter system set undo_tablespace=testundotbs;

System altered.
switch undo tablespace
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      TESTUNDOTBS
查看验证结果;
SQL> show user
USER is "HR"
SQL> select * from t;

        ID NAME
---------- --------------------
         0 arcerzhang

SQL> truncate table t;

Table truncated.

SQL> select * from t;

no rows selected

SQL> begin
  2      for i in 1..1000000
  3      loop
  4         insert into t values(i,'arcerzhang');
  5      end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'TESTUNDOTBS'
ORA-06512: at line 4
以hr用户进行测试;

little demo

由于undo tablespace size过小,导致查询失败;ora-01555错误;

看图通晓原理

关于undo tablespace size的调整 

SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
                                   2.08833333

SQL> 
最大值算法:计算每秒钟需要的undoblks;最大值算法;
SQL> select sum(undoblks) / sum((end_time - begin_time)*24*3600) from v$undostat;

SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*24*3600)
------------------------------------------------
                                      .062157134

SQL> 
平均值算法:计算每秒钟需要的undoblks;
  • Determining a size for the UNDO tablespace requires three pieces of information:
    • (UR)UNDO_RETENTION in seconds
    • (UPS)Number of undo data blocks generated per second
    • (DBS)Overhead varies based on extent and file size(db_block_size)

计算undo tablespace size的方法一(最大值算法):参数1 * 参数2 * 参数3

SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
                                   2.08833333
参数1
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
SQL> 
参数2
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
参数3

计算undo tablespace size的方法二(平均值算法):参数1 * 参数2 * 参数3

SQL> select sum(undoblks) / sum((end_time - begin_time)*24*3600) from v$undostat;

SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*24*3600)
------------------------------------------------
                                      .062157134
参数1
SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
SQL> 
参数2
SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
参数3

undo tablespace quota-使用undo tablepsace quota来进行undo tablespace的管理

背景:undo tablespace事关全局,每个用户不可能在一个时间点占用全部的undo tablespace,这就牵扯到undo tablespace quota的问题.对于undo tablespace quota的问题,主要与undo_pool参数有关,属于resource manager中的内容;

•Long transactions and improperly written transactions can consume valuable resources.
•With undo quota,users can be grouped and a maximum undo space limit can be assigned to the group.
•UNDO_POOL,a Resource Manager directive,defines the amount of space allowed for a resource group.
•When a group exceeds its limit,no new transactions are possible for the group,until undo space is freed by current transactio which are either completing or aborting.

undo segment可以被多个transaction所共享; 

Get Undo Segment Info

  • Information about undo segments can be obtained by querying the following views: ---DBA_ROLLBACK_SEGS
  • SQL> select segment_name,tablespace_name from dba_rollback_segs;
    
    SEGMENT_NAME                   TABLESPACE_NAME
    ------------------------------ ------------------------------
    SYSTEM                         SYSTEM
    _SYSSMU10_3271578125$          UNDOTBS1
    _SYSSMU9_3945653786$           UNDOTBS1
    _SYSSMU8_3612859353$           UNDOTBS1
    _SYSSMU7_4222772309$           UNDOTBS1
    _SYSSMU6_3654194381$           UNDOTBS1
    _SYSSMU5_4011504098$           UNDOTBS1
    _SYSSMU4_1126976075$           UNDOTBS1
    _SYSSMU3_4004931649$           UNDOTBS1
    _SYSSMU2_111974964$            UNDOTBS1
    _SYSSMU1_1240252155$           UNDOTBS1
    
    SEGMENT_NAME                   TABLESPACE_NAME
    ------------------------------ ------------------------------
    _SYSSMU20_383735042$           MYUNDOTBS
    _SYSSMU19_3345254938$          MYUNDOTBS
    _SYSSMU18_1415882764$          MYUNDOTBS
    _SYSSMU17_3557938328$          MYUNDOTBS
    _SYSSMU16_1859217099$          MYUNDOTBS
    _SYSSMU15_1154943744$          MYUNDOTBS
    _SYSSMU14_1909609757$          MYUNDOTBS
    _SYSSMU13_3542379076$          MYUNDOTBS
    _SYSSMU12_1217287258$          MYUNDOTBS
    _SYSSMU11_1756135178$          MYUNDOTBS
    _SYSSMU28_3554829085$          TESTUNDOTBS
    
    SEGMENT_NAME                   TABLESPACE_NAME
    ------------------------------ ------------------------------
    _SYSSMU27_2132966396$          TESTUNDOTBS
    _SYSSMU26_3800527619$          TESTUNDOTBS
    _SYSSMU25_1675500269$          TESTUNDOTBS
    _SYSSMU24_1510368484$          TESTUNDOTBS
    _SYSSMU23_2344035146$          TESTUNDOTBS
    _SYSSMU22_702459267$           TESTUNDOTBS
    _SYSSMU21_544576415$           TESTUNDOTBS
    
    29 rows selected.
    
    SQL> 
    View Code
  • Dynamic Performance Views
    • V$ROLLNAME
    • SQL> select * from v$rollname;
      
             USN NAME
      ---------- ------------------------------
               0 SYSTEM
              21 _SYSSMU21_544576415$
              22 _SYSSMU22_702459267$
              23 _SYSSMU23_2344035146$
              24 _SYSSMU24_1510368484$
              25 _SYSSMU25_1675500269$
              26 _SYSSMU26_3800527619$
              27 _SYSSMU27_2132966396$
              28 _SYSSMU28_3554829085$
      
      9 rows selected.
      View Code
    • V$ROLLSTAT(在线undo tablespace信息)
    • SQL> select usn,status from v$rollstat;
      
             USN STATUS
      ---------- ---------------
               0 ONLINE
              21 ONLINE
              22 ONLINE
              23 ONLINE
              24 ONLINE
              25 ONLINE
              26 ONLINE
              27 ONLINE
              28 ONLINE
      
      9 rows selected.
      View Code
    • V$UNDOSTAT
    • SQL> select begin_time,end_time,undoblks from v$undostat;
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 21:31:53 2013/07/16 21:36:01          4
      2013/07/16 21:21:53 2013/07/16 21:31:53         17
      2013/07/16 21:11:53 2013/07/16 21:21:53         13
      2013/07/16 21:01:53 2013/07/16 21:11:53         14
      2013/07/16 20:51:53 2013/07/16 21:01:53         48
      2013/07/16 20:41:53 2013/07/16 20:51:53         13
      2013/07/16 20:31:53 2013/07/16 20:41:53         20
      2013/07/16 20:21:53 2013/07/16 20:31:53         14
      2013/07/16 20:11:53 2013/07/16 20:21:53         13
      2013/07/16 20:01:53 2013/07/16 20:11:53         22
      2013/07/16 19:51:53 2013/07/16 20:01:53         43
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 19:41:53 2013/07/16 19:51:53         11
      2013/07/16 19:31:53 2013/07/16 19:41:53         14
      2013/07/16 19:21:53 2013/07/16 19:31:53         14
      2013/07/16 19:11:53 2013/07/16 19:21:53         15
      2013/07/16 19:01:53 2013/07/16 19:11:53         14
      2013/07/16 18:51:53 2013/07/16 19:01:53         47
      2013/07/16 18:41:53 2013/07/16 18:51:53         13
      2013/07/16 18:31:53 2013/07/16 18:41:53         14
      2013/07/16 18:21:53 2013/07/16 18:31:53         16
      2013/07/16 18:11:53 2013/07/16 18:21:53         18
      2013/07/16 18:01:53 2013/07/16 18:11:53         15
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 17:51:53 2013/07/16 18:01:53         48
      2013/07/16 17:41:53 2013/07/16 17:51:53         14
      2013/07/16 17:31:53 2013/07/16 17:41:53         13
      2013/07/16 17:21:53 2013/07/16 17:31:53         17
      2013/07/16 17:11:53 2013/07/16 17:21:53         16
      2013/07/16 17:01:53 2013/07/16 17:11:53         18
      2013/07/16 16:51:53 2013/07/16 17:01:53         48
      2013/07/16 16:41:53 2013/07/16 16:51:53         14
      2013/07/16 16:31:53 2013/07/16 16:41:53         31
      2013/07/16 16:21:53 2013/07/16 16:31:53         17
      2013/07/16 16:11:53 2013/07/16 16:21:53         18
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 16:01:53 2013/07/16 16:11:53         13
      2013/07/16 15:51:53 2013/07/16 16:01:53        111
      2013/07/16 15:41:53 2013/07/16 15:51:53         13
      2013/07/16 15:31:53 2013/07/16 15:41:53         18
      2013/07/16 15:21:53 2013/07/16 15:31:53         12
      2013/07/16 15:11:53 2013/07/16 15:21:53         16
      2013/07/16 15:01:53 2013/07/16 15:11:53         17
      2013/07/16 14:51:53 2013/07/16 15:01:53        116
      2013/07/16 14:41:53 2013/07/16 14:51:53         13
      2013/07/16 14:31:53 2013/07/16 14:41:53         15
      2013/07/16 14:21:53 2013/07/16 14:31:53         13
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 14:11:53 2013/07/16 14:21:53         16
      2013/07/16 14:01:53 2013/07/16 14:11:53         20
      2013/07/16 13:51:53 2013/07/16 14:01:53        101
      2013/07/16 13:41:53 2013/07/16 13:51:53         15
      2013/07/16 13:31:53 2013/07/16 13:41:53         15
      2013/07/16 13:21:53 2013/07/16 13:31:53         13
      2013/07/16 13:11:53 2013/07/16 13:21:53         17
      2013/07/16 13:01:53 2013/07/16 13:11:53         16
      2013/07/16 12:51:53 2013/07/16 13:01:53        115
      2013/07/16 12:41:53 2013/07/16 12:51:53         14
      2013/07/16 12:31:53 2013/07/16 12:41:53         15
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 12:21:53 2013/07/16 12:31:53         14
      2013/07/16 12:11:53 2013/07/16 12:21:53         17
      2013/07/16 12:01:53 2013/07/16 12:11:53         12
      2013/07/16 11:51:53 2013/07/16 12:01:53        160
      2013/07/16 11:41:53 2013/07/16 11:51:53         13
      2013/07/16 11:31:53 2013/07/16 11:41:53         25
      2013/07/16 11:21:53 2013/07/16 11:31:53         12
      2013/07/16 11:11:53 2013/07/16 11:21:53         17
      2013/07/16 11:01:53 2013/07/16 11:11:53         15
      2013/07/16 10:51:53 2013/07/16 11:01:53         97
      2013/07/16 10:41:53 2013/07/16 10:51:53         17
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 10:31:53 2013/07/16 10:41:53         12
      2013/07/16 10:21:53 2013/07/16 10:31:53         15
      2013/07/16 10:11:53 2013/07/16 10:21:53         18
      2013/07/16 10:01:53 2013/07/16 10:11:53         12
      2013/07/16 09:51:53 2013/07/16 10:01:53        121
      2013/07/16 09:41:53 2013/07/16 09:51:53         16
      2013/07/16 09:31:53 2013/07/16 09:41:53         18
      2013/07/16 09:21:53 2013/07/16 09:31:53         19
      2013/07/16 09:11:53 2013/07/16 09:21:53         13
      2013/07/16 09:01:53 2013/07/16 09:11:53         18
      2013/07/16 08:51:53 2013/07/16 09:01:53         83
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 08:41:53 2013/07/16 08:51:53         16
      2013/07/16 08:31:53 2013/07/16 08:41:53         14
      2013/07/16 08:21:53 2013/07/16 08:31:53         13
      2013/07/16 08:11:53 2013/07/16 08:21:53         13
      2013/07/16 08:01:53 2013/07/16 08:11:53         11
      2013/07/16 07:51:53 2013/07/16 08:01:53        109
      2013/07/16 07:41:53 2013/07/16 07:51:53         13
      2013/07/16 07:31:53 2013/07/16 07:41:53         14
      2013/07/16 07:21:53 2013/07/16 07:31:53         21
      2013/07/16 07:11:53 2013/07/16 07:21:53         14
      2013/07/16 07:01:53 2013/07/16 07:11:53         13
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 06:51:53 2013/07/16 07:01:53         91
      2013/07/16 06:41:53 2013/07/16 06:51:53         12
      2013/07/16 06:31:53 2013/07/16 06:41:53         16
      2013/07/16 06:21:53 2013/07/16 06:31:53         15
      2013/07/16 06:11:53 2013/07/16 06:21:53         16
      2013/07/16 06:01:53 2013/07/16 06:11:53         13
      2013/07/16 05:51:53 2013/07/16 06:01:53        101
      2013/07/16 05:41:53 2013/07/16 05:51:53         13
      2013/07/16 05:31:53 2013/07/16 05:41:53         14
      2013/07/16 05:21:53 2013/07/16 05:31:53         18
      2013/07/16 05:11:53 2013/07/16 05:21:53         12
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      
      2013/07/16 05:01:53 2013/07/16 05:11:53         10
      2013/07/16 04:51:53 2013/07/16 05:01:53        111
      2013/07/16 04:41:53 2013/07/16 04:51:53         15
      2013/07/16 04:31:53 2013/07/16 04:41:53         14
      2013/07/16 04:21:53 2013/07/16 04:31:53         12
      2013/07/16 04:11:53 2013/07/16 04:21:53         19
      2013/07/16 04:01:53 2013/07/16 04:11:53         16
      2013/07/16 03:51:53 2013/07/16 04:01:53         92
      2013/07/16 03:41:53 2013/07/16 03:51:53         15
      2013/07/16 03:31:53 2013/07/16 03:41:53         14
      2013/07/16 03:21:53 2013/07/16 03:31:53         14
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 03:11:53 2013/07/16 03:21:53         15
      2013/07/16 03:01:53 2013/07/16 03:11:53         15
      2013/07/16 02:51:53 2013/07/16 03:01:53         93
      2013/07/16 02:41:53 2013/07/16 02:51:53         11
      2013/07/16 02:31:53 2013/07/16 02:41:53         17
      2013/07/16 02:21:53 2013/07/16 02:31:53         15
      2013/07/16 02:11:53 2013/07/16 02:21:53         22
      2013/07/16 02:01:53 2013/07/16 02:11:53         12
      2013/07/16 01:51:53 2013/07/16 02:01:53        107
      2013/07/16 01:41:53 2013/07/16 01:51:53         13
      2013/07/16 01:31:53 2013/07/16 01:41:53         12
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/16 01:21:53 2013/07/16 01:31:53         15
      2013/07/16 01:11:53 2013/07/16 01:21:53         14
      2013/07/16 01:01:53 2013/07/16 01:11:53         31
      2013/07/16 00:51:53 2013/07/16 01:01:53        252
      2013/07/16 00:41:53 2013/07/16 00:51:53         15
      2013/07/16 00:31:53 2013/07/16 00:41:53         13
      2013/07/16 00:21:53 2013/07/16 00:31:53         13
      2013/07/16 00:11:53 2013/07/16 00:21:53         16
      2013/07/16 00:01:53 2013/07/16 00:11:53         21
      2013/07/15 23:51:53 2013/07/16 00:01:53        110
      2013/07/15 23:41:53 2013/07/15 23:51:53         14
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 23:31:53 2013/07/15 23:41:53         13
      2013/07/15 23:21:53 2013/07/15 23:31:53         18
      2013/07/15 23:11:53 2013/07/15 23:21:53         18
      2013/07/15 23:01:53 2013/07/15 23:11:53         16
      2013/07/15 22:51:53 2013/07/15 23:01:53        118
      2013/07/15 22:41:53 2013/07/15 22:51:53         14
      2013/07/15 22:31:53 2013/07/15 22:41:53         14
      2013/07/15 22:21:53 2013/07/15 22:31:53         19
      2013/07/15 22:11:53 2013/07/15 22:21:53         17
      2013/07/15 22:01:53 2013/07/15 22:11:53         28
      2013/07/15 21:51:53 2013/07/15 22:01:53       1253
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 21:41:53 2013/07/15 21:51:53         17
      2013/07/15 21:31:53 2013/07/15 21:41:53         10
      2013/07/15 21:21:53 2013/07/15 21:31:53         13
      2013/07/15 21:11:53 2013/07/15 21:21:53         16
      2013/07/15 21:01:53 2013/07/15 21:11:53         12
      2013/07/15 20:51:53 2013/07/15 21:01:53         89
      
      2013/07/15 20:41:53 2013/07/15 20:51:53         14
      2013/07/15 20:31:53 2013/07/15 20:41:53         13
      2013/07/15 20:21:53 2013/07/15 20:31:53         12
      2013/07/15 20:11:53 2013/07/15 20:21:53         15
      2013/07/15 20:01:53 2013/07/15 20:11:53         14
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 19:51:53 2013/07/15 20:01:53         94
      2013/07/15 19:41:53 2013/07/15 19:51:53         17
      2013/07/15 19:31:53 2013/07/15 19:41:53         13
      2013/07/15 19:21:53 2013/07/15 19:31:53         16
      2013/07/15 19:11:53 2013/07/15 19:21:53         18
      2013/07/15 19:01:53 2013/07/15 19:11:53         14
      2013/07/15 18:51:53 2013/07/15 19:01:53         93
      2013/07/15 18:41:53 2013/07/15 18:51:53         14
      2013/07/15 18:31:53 2013/07/15 18:41:53         12
      2013/07/15 18:21:53 2013/07/15 18:31:53         19
      2013/07/15 18:11:53 2013/07/15 18:21:53         16
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 18:01:53 2013/07/15 18:11:53         11
      2013/07/15 17:51:53 2013/07/15 18:01:53         95
      2013/07/15 17:41:53 2013/07/15 17:51:53         12
      2013/07/15 17:31:53 2013/07/15 17:41:53         13
      2013/07/15 17:21:53 2013/07/15 17:31:53         14
      2013/07/15 17:11:53 2013/07/15 17:21:53         14
      2013/07/15 17:01:53 2013/07/15 17:11:53         10
      2013/07/15 16:51:53 2013/07/15 17:01:53        118
      2013/07/15 16:41:53 2013/07/15 16:51:53         11
      2013/07/15 16:31:53 2013/07/15 16:41:53         17
      2013/07/15 16:21:53 2013/07/15 16:31:53         16
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 16:11:53 2013/07/15 16:21:53         19
      2013/07/15 16:01:53 2013/07/15 16:11:53         12
      2013/07/15 15:51:53 2013/07/15 16:01:53         92
      2013/07/15 15:41:53 2013/07/15 15:51:53         16
      2013/07/15 15:31:53 2013/07/15 15:41:53         13
      2013/07/15 15:21:53 2013/07/15 15:31:53         13
      2013/07/15 15:11:53 2013/07/15 15:21:53         15
      2013/07/15 15:01:53 2013/07/15 15:11:53         14
      2013/07/15 14:51:53 2013/07/15 15:01:53        109
      2013/07/15 14:41:53 2013/07/15 14:51:53         14
      2013/07/15 14:31:53 2013/07/15 14:41:53         14
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 14:21:53 2013/07/15 14:31:53         12
      2013/07/15 14:11:53 2013/07/15 14:21:53         15
      2013/07/15 14:01:53 2013/07/15 14:11:53         12
      2013/07/15 13:51:53 2013/07/15 14:01:53         94
      2013/07/15 13:41:53 2013/07/15 13:51:53         18
      2013/07/15 13:31:53 2013/07/15 13:41:53         12
      2013/07/15 13:21:53 2013/07/15 13:31:53         17
      2013/07/15 13:11:53 2013/07/15 13:21:53         14
      2013/07/15 13:01:53 2013/07/15 13:11:53         12
      2013/07/15 12:51:53 2013/07/15 13:01:53        101
      2013/07/15 12:41:53 2013/07/15 12:51:53         16
      
      BEGIN_TIME          END_TIME              UNDOBLKS
      ------------------- ------------------- ----------
      2013/07/15 12:31:53 2013/07/15 12:41:53         12
      2013/07/15 12:21:53 2013/07/15 12:31:53         15
      2013/07/15 12:11:53 2013/07/15 12:21:53         16
      2013/07/15 12:01:53 2013/07/15 12:11:53         87
      2013/07/15 11:51:53 2013/07/15 12:01:53         22
      
      203 rows selected.
      
      SQL>
      View Code
    • V$SESSION(常规)
    • V$TRANSACTION(常规)

oracle数据库crash时前滚与回滚的区别

数据库的记录先写到log然后写到datafile,这样的话就存在一些redo已经写到logfile但是还没有写到datafile内,log中存储了transaction信息,包括commited or uncommited data。可能这些修改信息并没有被oracle正确的来处理,包含两种情况:已经提交的还没有写入数据文件,或者没有提交的却被写入了数据文件。此时如果instance crash了,在实例恢复时针对已经提交的还没有写入数据文件就要发生前滚,在前滚过程中,smon会根据online redo log中的记录来完成对datafile的修改。保证已经提交的数据写入数据文件datafile。
接下来,前滚结束后,数据库正常open,此时用户可以正常连接,可以访问已经recover的commited data,但是对于那些属于unrecoverable transaction的uncommited data,会被oracle 加锁,是不可以访问的。rolling back:假如有进程访问这些加锁的data,此时smon会对这些数据块做rollback回滚,从数据文件中撤销没有提交却被写入数据文件的数据。

/*如何实现undo segments and space 的自动化管理*/

1、创建一个undo_tablespace,名称为undo_tbs1

2、设置参数undo_tablespace

SQL> show parameter undo_tabl

NAME                                 TS Type      VALUE
------------------------------------ ------------ ------------------------------
undo_tablespace                      string       UNDOTBS1
View Code

3、设置参数undo_management

SQL> show parameter undo_manage

NAME                                 TS Type      VALUE
------------------------------------ ------------ ------------------------------
undo_management                      string       AUTO
View Code

 

 

 

posted @ 2013-07-16 13:06  ArcerZhang  阅读(279)  评论(0编辑  收藏  举报